Pfthb

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.

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).

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.

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: ]);