Access sql query based on user provided date only returns dates with times = 0:00:00

1
votes

Haven't found any closely related questions, so here goes.

This is a simple version of my query:

SELECT sampleID, beginDateTime
FROM sample
WHERE DateValue(beginDateTime) = [Enter sample date: ];

This returns no records but should return 3 based on the date I entered.

However, this version returns what I expect, all the records that match the supplied date and their respective times. 

SELECT sampleID, beginDateTime
FROM sample
WHERE DateValue(beginDateTime) = #2015-5-18#;

How can I enable a user prompt that is properly formatted in Access SQL that will return the appropriate records? Note: I don't know VBA.

Also tried adding PARAMETER = beginDateTime DateTime; This seems to return all records and assigns them all the date that the user was prompted for.

Tried using DateValue in the parameter list:

SELECT sampleID, DateValue(beginDateTime)
FROM sample
WHERE beginDateTime = [Enter sample date: ];

This only returns dates = 0:00:00

I'd like my query to prompt the user for a date (without a time) and return all records with that date along with their respective times.

I would appreciate your help very much. Thanks in advance.

3
sqldatems-accessprompt

3 Answers

1
votes

Building on @Gustav's suggestion to use a typed parameter (which has the added benefit that Access will refuse a non valid date): 

PARAMETER [Enter sample date: ] DateTime;
SELECT sampleID, beginDateTime
FROM sample
WHERE beginDateTime >= [Enter sample date: ] and beginDateTime < ([Enter sample date: ]+1)

This way you avoid filtering on an expression, which disables index usage, and therefore would be slow on large datasets.

Edit: To achieve this in the interface, just open your query in design view, then go to Design, Parameters, and enter the name and data type of your parameter(s).

1
votes

Specify the parameter using the correct syntax:

PARAMETERS [Enter sample date: ] DateTime;
SELECT sampleID, beginDateTime
FROM sample
WHERE DateValue(beginDateTime) = [Enter sample date: ];

Then it will accept a date entered as to the format of your current Windows settings.

0
votes

You could try converting the value coming back from the input box to a date:

SELECT sampleID, beginDateTime
  FROM sample
 WHERE DateValue(beginDateTime) = CDate([Enter sample date: ]);