Fix Date Criteria in Microsoft Access Crosstab Queries
Question details
The user needs an Access crosstab query to recognize a date criterion, including when the date is supplied from VBA.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A crosstab query uses DateValue for a row heading and filters results by a specified date.
- Observed behavior
- An undelimited date criterion can be interpreted as arithmetic and return no records. Adding hash characters to a VBA Date variable can cause a type mismatch.
Open the crosstab query in Access Design View or SQL View and identify the date expression and criterion. If VBA supplies the date, check whether the query uses a parameter or builds a SQL string.
Delimit a literal date in the crosstab query
Use this method when the criterion is a fixed date written directly in the query.
Access uses hash characters to identify date literals in SQL. Without them, a value such as 8/22/2020 can be interpreted as an arithmetic expression rather than a date.
In the Access Navigation Pane, right-click the crosstab query and select Design View.
In the Criteria row under the relevant date expression, enter a delimited date such as #8/22/2020# or #2020-08-22#. Alternatively, use the same literal in the appropriate condition in SQL View.
Select Run on the query design ribbon and check that records for the intended date appear.

Declare a DateTime parameter for the crosstab query
Use a typed parameter when the date changes between runs or is supplied by VBA.
Pass a typed Date value from VBA
Use this approach when a VBA module collects the date and runs a saved parameterized query.
Review exported crosstab results in WPS Spreadsheet
WPS Office is a free, lightweight Office alternative with a familiar interface and support for common Microsoft Office formats, making it easy to move exported Access results into a spreadsheet. It does not run or repair Access crosstab queries.

Frequently Asked Questions
Why does an Access date criterion return no records?
An undelimited literal such as 8/22/2020 can be evaluated as arithmetic instead of a date. In the query's Criteria row or SQL View, use a date literal such as #8/22/2020#.
How do I declare a date parameter in a crosstab query?
In Design View, select Parameters on the query design ribbon and set the parameter's data type to Date/Time. In SQL View, place a declaration such as PARAMETERS [Enter date] DateTime; before TRANSFORM.
Should I add hash characters to a VBA Date variable?
No. Hash characters delimit date literals in SQL text. For a parameterized query, assign the VBA Date variable directly to the query parameter.
Can WPS Office run an Access crosstab query?
No. Fix and run the query in Microsoft Access. You can export its results to an Excel file and open that file in WPS Spreadsheet for review.




