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