Date/Time criteria problem

[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

 

[Marshall Barton]

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#

 

[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#));

 

[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 ;