Prev: Group By Desing View
Next: open a message box if a query is null during an autoexec macro
From: Keith on 28 Apr 2010 11:55
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

From: Marshall Barton on 28 Apr 2010 12:45
Keith wrote:

>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#

--
Marsh
MVP [MS Access]
From: Daryl S on 28 Apr 2010 13:19
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#));

--
Daryl S


"Keith" wrote:

> 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
>
From: Jerry Whittle on 28 Apr 2010 13:26
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 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Keith" wrote:

> 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
>
 | 
Pages: 1
Prev: Group By Desing View
Next: open a message box if a query is null during an autoexec macro