How To Create A Crosstab Query In Access 2016
- Home
- Databases
- General Databases
I'm not an Access expert so I hope this makes sense. I inherited an Access database and I'm trying to modify it so it's a little more usable.
I have a query called Closed Cases that pulls information based on a date range. When run, it prompts the user, accepts the input and provides the desired data.
The problem is when a crosstab query is run it wants to use the Closed Cases query. It returns the error "The Microsoft Office Access database engine does not recognize '[Start date]' as a valid field name or expression."
I know it's using the query because when I hard code the date range in the query the cross tab runs fine.
What's the best way for the crosstab query to pull data based on a date range? Should the date range be in the crosstab query or the basic query?
Can you provide the SQL view text when you receive the original message of "The Microsoft Office Access database engine does not recognize '[Start date]' as a valid field name or expression."
I think you just need to add the parameters to the top of it like this...
- ROM
- CPU
- RAM
- GPU
13 Replies
Double check that you are using the correct field name. That message usually is a result of that.
Have you tried appending the field name to the table name: [tablename].[fieldname]
The field name is correct. the only thing I change is the hard coded date. when hard coded the crosstab works fine. When I change the basic query to prompt for a date as in >=[Start Date] And <=[End Date] the crosstab fails to work.
CraigP is on the right track
CraigP is correct, you have to create the parameter for a crosstab query to recognise it.
To the Parameters screen I added the name of the basic query, "Closed Cases". This also shows up as the Table Name in the Crosstab query. I set the data type to Date/Time. Now a error message appears "Parameter 'Closed Cases' specified where a table name is required." the attachment shows the Crosstab query.
Looks a little different than my view. When I am in the design view of the query the parameters are broken out into the Criteria with the statement ">=[Start Date] And <=[End Date]" (It did that automatically from the query). (attached image)
Did you setup the parameter screen like in the second image?
Can you provide the SQL view text when you receive the original message of "The Microsoft Office Access database engine does not recognize '[Start date]' as a valid field name or expression."
I think you just need to add the parameters to the top of it like this...
CraigP, thanks for your help. Adding the parameters statement to the crosstab query did the trick.
No problem. Glad I could help.
thank you so much..it work
What if the query is connected to a form like this Between [Forms]![ControlPage]![Text22] And [Forms]![ControlPage]![Text24] Text 22 is Start Date and Text 24 is End Date on another Form, Contorl page is another form which contains Start and End Date (text 22 and Text 24)
The Question is solved , I have another question I am using running sum on group and over all in my reports but soon as i use date range for specified period of time the function is calculated in and out of the selected period and previous balance reset to zero, I need you guys to help me on it I want to use date range but balance should not reset it should be in continue mode.
This topic has been locked by an administrator and is no longer open for commenting.
To continue this discussion, please ask a new question.
How To Create A Crosstab Query In Access 2016
Source: https://community.spiceworks.com/topic/370259-crosstab-query-in-access-not-prompting-for-date-range
Posted by: kerbereaceforehis.blogspot.com
0 Response to "How To Create A Crosstab Query In Access 2016"
Post a Comment