Date/Time criteria problem

K

Keith

I am trying to create a query that allows a user to select a specific date
for which to show results. Following is sample SQL with a fixed criteria:

SELECT qryShopJobsIO1.JOB_NO, qryShopJobsIO1.[TOTAL QUANTITY ORDERED],
qryShopJobsIO1.[ENTRY DATE], qryShopJobsIO1.SumOfTotalHours
FROM qryShopJobsIO1
WHERE (((qryShopJobsIO1.[ENTRY DATE])=#4/27/2010#));


This particular example returns no records. I know that I have records for
which the ENTRY DATE field is 4/27/2010. I believe that the problem is that
the default value of that field (in the table definition) is set to =Now()
(as opposed to Date()), which means that the date/time field probably
contains some time information. What can I do to "massage" the ENTRY DATE so
that I can use a single date criteria?

Thanks in advance,

Keith
 
M

Marshall Barton

Keith said:
I am trying to create a query that allows a user to select a specific date
for which to show results. Following is sample SQL with a fixed criteria:

SELECT qryShopJobsIO1.JOB_NO, qryShopJobsIO1.[TOTAL QUANTITY ORDERED],
qryShopJobsIO1.[ENTRY DATE], qryShopJobsIO1.SumOfTotalHours
FROM qryShopJobsIO1
WHERE (((qryShopJobsIO1.[ENTRY DATE])=#4/27/2010#));


This particular example returns no records. I know that I have records for
which the ENTRY DATE field is 4/27/2010. I believe that the problem is that
the default value of that field (in the table definition) is set to =Now()
(as opposed to Date()), which means that the date/time field probably
contains some time information. What can I do to "massage" the ENTRY DATE so
that I can use a single date criteria?

WHERE DateValue([ENTRY DATE])=#4/27/2010#
 
D

Daryl S

Keith -

You can use just the date portion of the ENTRY DATE in the WHERE string by
using the DateValue function, like this:

SELECT qryShopJobsIO1.JOB_NO, qryShopJobsIO1.[TOTAL QUANTITY ORDERED],
qryShopJobsIO1.[ENTRY DATE], qryShopJobsIO1.SumOfTotalHours
FROM qryShopJobsIO1
WHERE (((DateValue(qryShopJobsIO1.[ENTRY DATE]))=#4/27/2010#));
 
J

Jerry Whittle

SELECT qryShopJobsIO1.JOB_NO,
qryShopJobsIO1.[TOTAL QUANTITY ORDERED],
qryShopJobsIO1.[ENTRY DATE],
qryShopJobsIO1.SumOfTotalHours
FROM qryShopJobsIO1
WHERE qryShopJobsIO1.[ENTRY DATE]
Between #4/27/2010# And #4/27/2010# + 0.9999 ;

If you want the users to be prompted for the date:

PARAMETERS [Enter the Date] DateTime;
SELECT qryShopJobsIO1.JOB_NO,
qryShopJobsIO1.[TOTAL QUANTITY ORDERED],
qryShopJobsIO1.[ENTRY DATE],
qryShopJobsIO1.SumOfTotalHours
FROM qryShopJobsIO1
WHERE qryShopJobsIO1.[ENTRY DATE]
Between [Enter the Date] and [Enter the Date] + 0.9999 ;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top