Fix an Invalid Date Criterion in a Microsoft Access Query
Question details
The user needs to correct an Access query that uses form controls as the start and end dates in a Between criterion.

- 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.
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.
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.
In Access, right-click the query in the Navigation Pane and select Design View.
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].
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.

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.

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.




