Discussion Locked

Access Crosstab Filter

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I used the Northwind Sales by Year Report as an example for this report. The table I call is a stand-alone table that gets updated at the end of the month (Jan-Apr). I set up the query on the table and the parameters and date criteria to filter bybeginning date and ending date. The form is just a simple entry form with beginning and ending date with ok and cancel button. If I input the dates 1-1-02 and 4-30-02 the report runs fine. The problem is when I run the report for any of the previous month (ie. 3-31-02) as the ending date. I receive the message "The Microsoft Jet Database engine does not recognize " as a valid field name or expression." Any help would be greatly appreciated.

6 replies

by heartyguffaw

The problem may not be with the report, but with the query or the form dialog.

In the Northwind DB, the query criteria is based on the "Sales By Year Dialog" form. If you are doing something similar, to troubleshoot your problem, try this:

- Make sure your form dialog "Modal" setting is "No"
- Open the form and put in your dates
- Leaving the form open, run the query

The query should display only the dates your form has in it.

If you get the the same error message as before, itcould be format of your form dialog date fields or something in the query criteria referring to the form.

by shanghai sam

Let me expand a little more so that you have all the information. I have the form dialog as a Modal. The date field in the table and Beginning and Ending date in the form are all formatted as short date. I tested the form entering the dates and then opening the query and it worked fine. Any more suggestions?

by timfox

If you are using a crosstab query, your column headers may change depending on whether you are including nulls in your recordset result. That's all I can think of, in terms of how this error would be caused by a change in the dataset.

Have you tried troubleshooting this by testing the query with changes to your filter etc?

by shanghai sam

I have tested the query using the form filter, only parameters in the query, and the filter button and all work like they are suppose to. But when I run the report I get asking twice for the dates once by the parameters window of the query, and once by the filter form that I created. We only run the report at the end of the month and I assuming that if I do not enter a number for the month that it enters a blank not a zero. Am I wrong in this assumption?

by timfox

If you are using a crosstab query, your column headers may change depending on whether you are including nulls in your recordset result. That's all I can think of, in terms of how this error would be caused by a change in the dataset.

Have you tried troubleshooting this by testing the query with changes to your filter etc?

This discussion is closed to new replies.