Others

Fix Date Criteria in Microsoft Access Crosstab Queries

Tauseeq MagsiTauseeq Magsi Nov 13, 2026 869 views

Question details

The user needs an Access crosstab query to recognize a date criterion, including when the date is supplied from VBA.

Fix Date Criteria in Microsoft Access Crosstab Queries
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the query

In the Access Navigation Pane, right-click the crosstab query and select Design View.

2
Correct the criterion

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.

3
Run the query

Select Run on the query design ribbon and check that records for the intended date appear.

Delimit a literal date in the crosstab query
Use an unambiguous date: Write literals in US short-date format or YYYY-MM-DD format to avoid ambiguity. Hash characters delimit SQL literals; they are not part of a VBA Date value.
Free Microsoft Office alternative

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.

Review and filter crosstab results exported from AccessOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

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.