MS Access selecting records with a variable

Question

We are trying to make a query that has the user enter a date and the query pulls all records according to that date. In the Design View of the query we have tried [Enter Date] in criteria but it will not pull because it will match the date but not time. I have seen lots of dates formatted with ## marks to ignore the time yet I do not know how to incorporate that into our query.

Edit: Yes we are using Access which defaults to a date/time field.

We are impatient and are just going to do a range forcing the user to enter two dates. Honestly it takes care of two problems, but this was a frustrating issue with Access.

Answer 1

So you have time stamps in the database that you want to query by a single date?

There are two ways to do this:

1) Strip off the time part on the database field

SELECT * 
  FROM Table 
 WHERE DateValue([DateField]) = [Enter Date]

that's not very good performance wise but ok for small tables

2) you add times to your input date and use Between which is better performance wise

SELECT * 
  FROM table 
 WHERE [DateField] BETWEEN [Enter Date] AND [Enter Date] + #23:59:59#

Answer 2

I guess your data was basically entered in a dateTime format. In this case, you should build your query on datevalue(myDateField) instead of building it on myDateField. Your query will then look like that (in the SQL window)

SELECT * From MyTable WHERE dateValue(myDateField) = [enter date here]

instead of

SELECT * From MyTable WHERE myDateField = [enter date here]

Answer 3

I'd adjust DJ's suggestion about BETWEEN, because I never quite trust that it works the way I'd like it to (the reason for adding 1 second less than a day for the second value is because the second value is included in the BETWEEN range, which has always seemed counterintuitive to me).

Anyway, my suggestion:

  SELECT * 
    FROM table 
   WHERE [DateField] >= [Enter Date] 
         AND [DateField] < [Enter Date] + 1

It's not all that different, but it avoids the problem of adding the right amount to the second argument of BETWEEN. I am always wary of trying to do date/time math operations without using DateAdd() or DateDiff().

Defining the parameter is a good idea, too, as then if a user puts in an invalid date, they'll get a useful error message, instead of just the wrong results.