If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date/Time criteria problem
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 |
#2
|
|||
|
|||
Date/Time criteria problem
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] |
#3
|
|||
|
|||
Date/Time criteria problem
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 |
#4
|
|||
|
|||
Date/Time criteria problem
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 |
Thread Tools | |
Display Modes | |
|
|