Date Parameter in Query - Pulling incorrect results (Access 2010)

Question

I want to pull results based on beginning and end dates, as entered in as ask query.  My date criteria is as follows:

            Between [Start date (dd/mm/yyyy)] And [End Date (dd/mm/yyyy)]

The query runs fine, but the results are not restricted to the specified dates.  What am I doing wrong?

Barb

Answer 1

You need some separator characters between the parts of the date before the query can process it into a real date value.  See if it works when you enter 9 Sept 12 or use the style in the prompt 9/sept/12 or any number of equivalent date styles.

Alternatively, you could take the next step and use a form with text boxes to enter the start and end dates.  Then the query criteria would look like:

Between Forms!theform.txtstart And Forms!theform.txtend

Doing it this way gives you another option where you can use code to convert an undelimited date to a real date for the query to use.

Answer 2

Fantastic!  "01 sep 12" etc. works perfectly!  Thanks so much!

Answer 3

Regional setting is dd/mm/yyyy (Canadian).  Dates are entered as ddMMMyy and display that way.  However, the parameter query will not recognize date input of "09sep12".

Answer 4

Sounds like your regional setting is using mm/dd/yyyy style dates or maybe the date field in its table is Text data type instead of Date/Time data type.