Report Error Depends on Date Filter????

[streamranger]

Something very odd is happening -

I have a cross-tab query that is filtered on user-defined date range using a where condition of:
>[Forms]![Reports_Menu]![txtFilterDateFrom] AND <[Forms]![Reports_Menu]![txtFilterDateTo]

the Query functions perfectly. The report based on that query, however, only functions with a DateFrom that is before 1/28/14 and a DateTo that is after 2/27/14. If I enter a date in either field that is outside of those ranges, I get the following error:

The Microsoft Access database engine does not recognize " as a valid field name or expression.

I have defined both of my Query Parameters as Date/Time in the Xtab query design.

Any suggestions?

 

[pbaldy]

Do you have hard-coded field names in the report (control sources of text boxes, etc)? If the query works and the report throws the error, that would be my first suspect.

 

[streamranger]

:banghead:It appears to be the column headings in the report based on the Xtab query.

The reason the dates of the error are so specific seems to be that there are no data to summarize for one of the column headings. (The query summarizes # of each type of permit each engineer receives in a given time period. One of the engineers left, so has no data to summarize after January.) That seems to be throwing the report generation off.

How do I create a report with dynamic column headings based on the period selected by the query?

 

[streamranger]

Sorry, I just re-read my post and it isn't very clear. I'll try again...
The report is based on a cross-tab query with permit types as rows, and engineer's initials as columns. The number of applications received is summarized in the intersecting cells.

I found that deleting the summary cells resolved the problem (but resulted in a blank report). I also found inserting dummy records for the retired engineer later in the year also resolved the problem (but doesn't reflect reality).

My questions stands - How can I generate a report with only the columns needed to summarize the data in the requested date range, or one which can just leave columns blank if there are no records for that engineer during the time period?

 

[pbaldy]

I use a report pre-built with enough textboxes to handle the max number of columns possible and code in the open event that assigns the control sources, labels, etc based on what the query is returning. I can't post it since I got it from a book, but Googling "Access dynamic crosstab report" should find numerous similar methods, like:

http://www.experts-exchange.com/Database/MS_Access/Q_28370681.html

 

[streamranger]

Problem solved....
I found the solution elsewhere (on the Allen Browne site), but will share here, too.

Since the column headings are derived from a field, you only get fields relevant to the data, so if the criteria limit the records to a period when one of your column headings had no corresponding records, the field disappears, fine for the query, but the report is looking for that field....So, It gives the error.

Solution:
I had to enter all of the possible values (including blank "") into the Column Headings property of the Query Properties. Now the report works, but the query will have to be run independent of the report periodically to be sure there are not additional column headings that need to be added to the report.

Not perfect, but workable.

 

[streamranger]

Thanks for the suggestion, it is definitely more flexible than the solution I found!
I will try this on a different query/report on my development list.

Since my column headings are not likely to change very often for the report in question, my rudimentary solution should suffice.

 

[pbaldy]

Glad you got it working.