Query/Report Problem

[Joe Williams]

Hi

I have a query called ForecastReport. It uses a form to pick up beginning
and ending dates that it uses as the criteria for the report.The query runs
great, no problem.

Now I have a report that uses the ForecastReport as its data source. When I
run the report, I get no data found, even though the underlying query
retrieves data.

Any ideas?

- joe

 

[Allen Browne]

Are there calculated fields in your query?

If the query retrieves records but the report does not display any, it's
possible that Access is not undersanding the data type correctly. Therefore
the report is grouping/sorting/filtering differently than the query.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Joe Williams]

Allen,

Yes there are calculated fields but they are all set to the proper format
and the same issue ocurred.

What I found out later though is that my query has four fields used as
criteria, all pointing to fields on the formt that calls the report (beg
date, end date, facility, dept). I found that if I remove ONE of the
criteria fields then the report runs as expected.

Is there a limit to how many fields can be used as criteria for a report or
is there some other issue?

Thanks

- joe

 

[Allen Browne]

No problem with 4 fields.

The one that fails: is it a date? number?
If date, set its Format property to Short Date.
If Number, set its Format to General Number.
This helps Access understand the intended data type.

For date or numeric calculated fields in the query, setting their Format
property does nothing, and using the Format() function is worse (forces
Access to treat them as text). Instead use one of the type conversion
functions around the calculation, such as CCur(), CVDate(), CLng(), CDbl().