accessaspire219
Registered User.
- Local time
- Today, 17:44
- Joined
- Jan 16, 2009
- Messages
- 126
Hi,
I am getting error 3070 when I try to run a query based on criteria input by the user in a form. Here is a description of the structure of database:
1. Raw data tables from external source
2. Query aggregating the raw data
3. Query calculating performance metrics by month/Quarter/Year (each in a separate query)
4. Cross tab query to convert the months/quarters and year to column headings
5. Select query to merge month/Quarter/Year for a metric so that they are displayed next to each other (I had to this step as I have to aggregate data differently for monthly/quarterly/yearly metrics)
6. Union query to unite all the individual metric query so that I have one file with everything. (all metrics all months all quarters and all years)
Now I made a form so that the user can specify which month, quarter, year or person they want to view. The run button on the form invokes the #6 union query. (I did this expecting that running this query will cascade in to running of all the other queries (#1-#5)
I set the calculation queries (#3 above) to look at what the user entered in the form. This is what I put as criteria:
Like [Forms]![Performance_Reports].[month] & "*"
Thus my expectation was, when the run button is clicked all the queries (#1-#5) run and when #3 runs it will take the criteria input by the user in the form.
Now, when I run the (#3) query by itself by specifying the criteria in the form it runs fine.
But when I click the run button on the form I get error 3070 saying Microsoft Jet Database Engine does not recognize Like [Forms]![Performance_Reports].[month] & "*" as a valid field name or expression.
The same happens when I run query #4.
Does anyone know what is causing this error and what should I be doing differently to avoid it?
Thanks!!
I am getting error 3070 when I try to run a query based on criteria input by the user in a form. Here is a description of the structure of database:
1. Raw data tables from external source
2. Query aggregating the raw data
3. Query calculating performance metrics by month/Quarter/Year (each in a separate query)
4. Cross tab query to convert the months/quarters and year to column headings
5. Select query to merge month/Quarter/Year for a metric so that they are displayed next to each other (I had to this step as I have to aggregate data differently for monthly/quarterly/yearly metrics)
6. Union query to unite all the individual metric query so that I have one file with everything. (all metrics all months all quarters and all years)
Now I made a form so that the user can specify which month, quarter, year or person they want to view. The run button on the form invokes the #6 union query. (I did this expecting that running this query will cascade in to running of all the other queries (#1-#5)
I set the calculation queries (#3 above) to look at what the user entered in the form. This is what I put as criteria:
Like [Forms]![Performance_Reports].[month] & "*"
Thus my expectation was, when the run button is clicked all the queries (#1-#5) run and when #3 runs it will take the criteria input by the user in the form.
Now, when I run the (#3) query by itself by specifying the criteria in the form it runs fine.
But when I click the run button on the form I get error 3070 saying Microsoft Jet Database Engine does not recognize Like [Forms]![Performance_Reports].[month] & "*" as a valid field name or expression.
The same happens when I run query #4.
Does anyone know what is causing this error and what should I be doing differently to avoid it?
Thanks!!
