Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Blank report in two types of condition, Post Reply Post New Topic
Author Message
Greg.Harper
Newbie
Newbie


Joined: 20 Aug 2010
Location: United States
Online Status: Offline
Posts: 5
Quote Greg.Harper Replybullet Topic: Blank report in two types of condition,
    Posted: 20 Aug 2010 at 10:24am
Reports which pulled from Access 2000 database were working fine. We upgraded the database to the new version which was written in Access 2007. I had issues with connecting, but those issues were worked out. I have found two conditions which the reports now come back blank.
In this database, I have inactive records and active records. Without adding a condition specifying active it pulls all data, both active and inactive. When I specify active, the report is blank. If I specify inactive, it pulls inactive correctly.
In this database, I have records which have a date. Using Select Expert, I may set up a single date or a date range, to limit the selection of records. When I specify a date or range of dates, the report comes back blank. I have two error popups too. I can't paste the print screen pics though.
The first error says, Failed to retrieve data from the database.
The second error says,
Detail: ADO Error Code: 0x80040e14
Microsoft Access Database Engine
Description: Undefined function 'CONVERT' in expression
SQL state: 3085
Native Error: -530123806 Database vender code: -530123806

Let me know what you think I should try.

Thank you,
Greg Harper
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Aug 2010 at 2:37pm
Originally posted by Greg.Harper

When I specify active, the report is blank. If I specify inactive, it pulls inactive correctly.
 
Please explain both how to interpret the data and how you are doing this (e.g. param type and select statement)
Originally posted by Greg.Harper

When I specify a date or range of dates, the report comes back blank.
 
Please explain the data and the select statement for this also
IP IP Logged
Greg.Harper
Newbie
Newbie


Joined: 20 Aug 2010
Location: United States
Online Status: Offline
Posts: 5
Quote Greg.Harper Replybullet Posted: 23 Aug 2010 at 3:20am
For the active/inactive error:
I use only Select Expert. I am copying the SQL from select Expert.
{Asset.ID} <= "300-999-9999" and
{Asset.Active}
If this is allowed to refresh it comes back blank. The only method I have to see only active assets is to set u Select Expert without Asset.Active, allow it to refresh to find both active and inactive data, and then to use Select Expert to add the condition for active. I must not allow the refresh when ok'ing out of Select Expert or I get a blank report, but by using saved data I see only actives.

For the error in returning a date:
I use only Select Expert. I am copying the SQL statements from select Expert.

{Work.SuperID} = "deli" and
{Work.ID} in 127193 to 127446
This statement gets me any dates within the range of the Work.ID I supply.

{Work.ID} in 127193 to 127446 and
{Work.SuperID} = "deli" and
{Work.SchDate} = DateTime (2010, 08, 10, 00, 00, 00)
This statement will sort a given date, and would work for a range of dates. It will work if I use saved data from the previous statement. If allowed to refresh when ok'ing out of Select Expert, the report is blank.

Thank you
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Aug 2010 at 3:35am
if youi remove all of your select statement (return all rows) and refresh the report do you get a blank report as well?
IP IP Logged
Greg.Harper
Newbie
Newbie


Joined: 20 Aug 2010
Location: United States
Online Status: Offline
Posts: 5
Quote Greg.Harper Replybullet Posted: 23 Aug 2010 at 3:59am
If I remove the Select Expert selections and refresh, in both reports, I get the full list of data, as I should.

Thank you
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Aug 2010 at 4:16am
hmmm...
I am not sure on this. My initial guess was that something in the select statement is filtering everything out (basically doing what it is written to do but not what you wanted it to do) but perhaps you have a bad row of data in your set that it is choking on and dropping all of the rows on refresh. The CONVERT Expression might be a clue that this is happening here.
 
Do you have any joins or is everything from the single WORK table?
 
 
Anyone else have thoughts/ideas?
 
 
 
IP IP Logged
Greg.Harper
Newbie
Newbie


Joined: 20 Aug 2010
Location: United States
Online Status: Offline
Posts: 5
Quote Greg.Harper Replybullet Posted: 23 Aug 2010 at 4:33am
I have some reports which use joins in Crystal Reports in the links section. The two errors I have happen to either reports with joins or without.
If you mean joins in the database, I know there are various joins, but I cannot get at the view to gives answers to questions.

This all happened once we upgraded from this database written in Access 2000, to the new version written in Access 2007. I have to believe the trouble has to be in some of the changes in Access 2007.
I do not remember this happening in mdb database files, only once the accdb files were made. I read somewhere that compacting the accdb files make mdb files.
I still have the many-step method to pull the correct data, it takes many more steps.

Thank you
IP IP Logged
Greg.Harper
Newbie
Newbie


Joined: 20 Aug 2010
Location: United States
Online Status: Offline
Posts: 5
Quote Greg.Harper Replybullet Posted: 19 Oct 2010 at 2:08am
Problems solved!

By installing all of the service packs for XI, the problems have gone away.

Thanks!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.