Prompt for a Date in a query

[kengooch]

I was trying to do a quick query that would allow a person to be prompted for the date they wanted to see data... I tried using... "#" & [Enter Date] & "#" but it doesn't work. Can I give a user the ability to select a specific date with a quick query prompt?

 

[theDBguy]

What does "didn't work" mean? Didn't you/they get a prompt?

 

[kengooch]

Yes but when you put in a date, it gives you no results. In another tool I built a few years ago I used this...
Between (IIf(IsNull([Start Date m/d/yyyy]),#1/1/1900#,[Start Date m/d/yyyy])) And (IIf(IsNull([End Date m/d/yyyy]),#12/31/2200#,[End Date m/d/yyyy]))
which works but requires you to enter the date twice, or of course a range of dates as it was originally designed to do.

 

[theDBguy]

Yes but when you put in a date, it gives you no results. In another tool I built a few years ago I used this...
Between (IIf(IsNull([Start Date m/d/yyyy]),#1/1/1900#,[Start Date m/d/yyyy])) And (IIf(IsNull([End Date m/d/yyyy]),#12/31/2200#,[End Date m/d/yyyy]))
which works but requires you to enter the date twice, or of course a range of dates as it was originally designed to do.

Can you post the complete SQL statement for your query, including the date prompt?

 

[kengooch]

This is the one that doesn't work... It always generates not results. In the query box it says
"#" & [Enter Date] & "#"
The SQL is:
SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff FROM qEvntComplete WHERE (((qEvntComplete.vStfFullNm) Like "*" & [Staff Name] & "*") AND ((qEvntComplete.tEvAccSeq)="#" & [Enter Date] & "#")) ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

 

[theDBguy]

This is the one that doesn't work... It always generates not results. In the query box it says
"#" & [Enter Date] & "#"
The SQL is:
SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff FROM qEvntComplete WHERE (((qEvntComplete.vStfFullNm) Like "*" & [Staff Name] & "*") AND ((qEvntComplete.tEvAccSeq)="#" & [Enter Date] & "#")) ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

Okay, assuming the following returns all records.

SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff
FROM qEvntComplete
ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

We could try something like this:

SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff
FROM qEvntComplete
WHERE (((qEvntComplete.vStfFullNm) Like "'*" & [Staff Name] & "*'") AND ((qEvntComplete.tEvAccSeq)="#" & Format(CDate([Enter Date]),"yyyy-mm-dd") & "#"))
ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

Let us know what happens when you try that.

 

[kengooch]

Okay, assuming the following returns all records.

SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff
FROM qEvntComplete
ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

We could try something like this:

SELECT qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm, qEvntComplete.tEvAccSeq, qEvntComplete.tEvStDate, qEvntComplete.tAccName, qEvntComplete.tAccCat, qEvntComplete.tActX, qEvntComplete.tAccNatX, [taccnatx]-[tactx] AS cDiff
FROM qEvntComplete
WHERE (((qEvntComplete.vStfFullNm) Like "'*" & [Staff Name] & "*'") AND ((qEvntComplete.tEvAccSeq)="#" & Format(CDate([Enter Date]),"yyyy-mm-dd") & "#"))
ORDER BY qEvntComplete.tEvAccCat, qEvntComplete.vStfFullNm;

Let us know what happens when you try that.

Didn't work... I tried adjusting the format to m/d/yyyy and several other versions... but no luck

 

[theDBguy]

Didn't work... I tried adjusting the format to m/d/yyyy and several other versions... but no luck

Can you post a sample db with test data?

 

[CJ_London]

perhaps your tEvAccSeq field contains a time element, possibly hidden by formatting

 

[Pat Hartman]

#1 - NEVER open a query for the user to interact with. Always use a form so you can prevent accidental updates.

It may seem "simple" to do what you are asking and you certainly do it your way. But what you end up with is an accident waiting to happen.

Prompts re difficult to control. The better solution is to add a control and a button on a form. Let the user enter the date and press the button. YOU validate the date and if it is a valid date, open the form filtered to the requested date.