Unable to query data between particular times. Please Help!!! 1

[mlrmlr]

Hello,

I am in desperate need of figuring out this time issue. >:-<

I really need to extract data between dates (using parameters)and between the times of 8:30 AM and 8:30 AM of the next day. I have tried the between function but it is not pulling all of the records.

Here is my current parameter:

Between [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![BeginDate] And [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![EndDate]

I am using the following date and time fields:

Enter_date
Enter_time

I actually used "<=8:30:00 AM" in my query and received the following output:

You can see (dates bolded) where it is missing some records between the two dates. Records recorded for the times in between 7:00 PM (6/16) thru 9:00 AM (6/17) are missing.

<BEGIN OUTPUT>
Enter Date Enter Time shift
6/16/2006 9:12:42 AM A
6/16/2006 9:49:38 AM A
6/16/2006 10:38:32 AM A
6/16/2006 11:15:52 AM A
6/16/2006 11:20:53 AM A
6/16/2006 12:31:24 PM A
6/16/2006 12:33:33 PM A
6/16/2006 12:34:06 PM A
6/16/2006 12:40:00 PM A
6/16/2006 2:28:49 PM A
6/16/2006 2:35:25 PM A
6/16/2006 2:36:47 PM A
6/16/2006 3:06:58 PM B
6/16/2006 3:11:43 PM A
6/16/2006 3:42:33 PM B
6/16/2006 3:43:35 PM B
6/16/2006 4:30:00 PM B
6/16/2006 5:05:00 PM B
6/16/2006 5:49:00 PM B
6/16/2006 5:52:55 PM B
6/16/2006 6:02:51 PM B
6/16/2006 6:08:37 PM B
6/16/2006 6:12:12 PM B
6/17/2006 9:07:51 AM C
6/17/2006 9:09:07 AM C
6/17/2006 9:09:13 AM C
<END OUTPUT>

We have three shifts:
A=8:00 AM - 4:00 PM
B=4:00 PM - 12:00 AM
C=12:00 AM - 8:00 AM

I would really appreciate any help I can get. I have been at this for awhile and do not have the advance experience to resolve this.

Thanks to anyone who can help.

 

[PHV]

Provided that the BeginDate and EndDate are full DateTime values (SQL WHERE clause snippet):
WHERE Enter_date+Enter_time Between [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![BeginDate] And [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![EndDate]

If the controls value are only Date:
WHERE Enter_date+Enter_time Between [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![BeginDate]+#08:30:00 AM# And [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![EndDate]+1+#08:29:59 AM#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mlrmlr]

PH,

Thank you soo much for tackling this issue for me.

I copied your Where clause in SQL and it convered the code to the following:

WHERE ((([Enter_date]+[Enter_time]) Between [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![BeginDate]+#12/30/1899 8:30:0# And [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![EndDate]+1+#12/30/1899 8:29:59#));

When I went to execute the query it returnes with the following error message:

"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".

Thanks again.

 

[PHV]

Add the following as the very first line of the SQL code:
PARAMETERS [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![BeginDate] DateTime, [Forms]![criteria_export_trouble_tkt_records_rpt_dialog]![EndDate] DateTime;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

and remove the 12/30/1899 from the query.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[mlrmlr]

PHV,

Thank you soooo much for your assistance. Your query worked (of course).

Leslie, thank you also for your input.

This is a good day! :-D