Others

Fix an Invalid Date Criterion in a Microsoft Access Query

Muhammad TalhaMuhammad Talha Nov 20, 2026 869 views

Question details

The user needs to correct an Access query that uses form controls as the start and end dates in a Between criterion.

Fix an Invalid Date Criterion in a Microsoft Access Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A query filters transactions between dates supplied by controls on a form.
Observed behavior
The query reports an invalid date value when the form control references are enclosed in number signs.
Before you start

Open the form that supplies the dates and enter valid start and end dates. Confirm the form and control names used in the query match their actual names.

Solution 1Recommended

Remove Number Signs from the Form Control References

Use the form control values directly in the Between criterion instead of treating the references as date literals.

1
Open the query in Design View

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

2
Edit the date criterion

In the Criteria row under the date field, remove the number signs around the form references. For the example form and controls, enter: Between Forms![Form-Select Tenfold Transactions]![selBegDate] And Forms![Form-Select Tenfold Transactions]![selEndDate].

3
Run the query

Keep the date-selection form open, enter the desired dates, and select Run on the Query Design tab. Check that the results fall within the selected range.

Remove Number Signs from the Form Control References
When number signs are used: Access uses number signs to delimit literal date values, not references to values in form controls.
Free Microsoft Office alternative

Work with Transaction Dates in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheet files. Open compatible Excel files to review and filter transaction dates; WPS does not fix Microsoft Access queries or run Access databases.

Filter spreadsheet transactions by dateOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interfaceMove compatible spreadsheet files without changing formats
microsoft office alternative - wps office

Frequently Asked Questions

Why does Access reject the date criterion with number signs?

Number signs delimit literal dates in Access expressions. A form control reference supplies a value, so write it directly in the Criteria row without number signs.

Does the date-selection form need to be open?

Yes. Open the form and enter the start and end dates before running a query that refers to its controls.

What should I check if the query still prompts for a value?

In Query Design View, check that the form name and control names in the Criteria row match the names in the form. Also confirm that the form is open.

Does Between include the start and end dates?

Between includes both boundary values. If the date field contains times, an end date at midnight may exclude records later on that day.