Converting Time Issue 1

[mlrmlr]

Hi,

I desperately need help on a time issue.

I have an Access query that exports data to an Excel spreadsheet (so far so good). Unfortunately, the data is exported incorrectly. Here is the problem…
I need the data to export for shifts “B” and “C” between particular times.

Example:

Shift B: Export data on 3/10/06 between 4:00 PM and 11:59 PM
Shift C: Export data on 3/11/06 between 12:00 AM and 8:30 AM

Currently, the data for “C” shift is exported for the incorrect date (3/10/06) and times.

I am not strong in SQL and would appreciate some assistance.

Thank you!

 

[PHV]

Any chance you could post your actual SQL code ?

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

 

[Golom]

Possibly a UNION query?

Select ShiftB As [Shift], ... other Fields ...
From myTable
Where ShiftDate BETWEEN #03/10/06 16:00:00# 
                    AND #03/10/06 23:59:00#

UNION ALL

Select ShiftC As [Shift], ... other Fields ...
From myTable
Where ShiftDate BETWEEN #03/11/06 00:00:00# 
                    AND #03/11/06 08:30:00#

 

[mlrmlr]

Here is the SQL code. Thank you.

SELECT wo_log_table.enter_date, wo_log_table.enter_time, wo_log_table.wo_activity_item, wo_log_table.site_id, wo_log_table.wo_comments
FROM wo_log_table
WHERE (((wo_log_table.enter_date) Between [Forms]![criteria_export_wo_tkt_records_dialog_form]![BeginDate] And [Forms]![criteria_export_wo_tkt_records_dialog_form]![EndDate]) AND ((wo_log_table.wo_activity_item)="WO System" Or
(wo_log_table.wo_activity_item)="Temp Alarm"
ORDER BY wo_log_table.enter_date, wo_log_table.enter_time;

 

[PHV]

No syntax error with this SQL code ?
For me the parenthesis aren't balanced ...
Anyway, what are shifts “B” and “C” (I guess "WO System" and "Temp Alarm") ?
How the query is aware of two different DateTime intervals ?


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

 

[Golom]

SELECT enter_date, enter_time, wo_activity_item, site_id, wo_comments

FROM wo_log_table

WHERE enter_date Between 
    [Forms]![criteria_export_wo_tkt_records_dialog_form]![BeginDate] 
And [Forms]![criteria_export_wo_tkt_records_dialog_form]![EndDate]

AND wo_activity_item = 'WO System' 

UNION ALL

SELECT enter_date, enter_time, wo_activity_item, site_id, wo_comments

FROM wo_log_table

WHERE enter_date Between [COLOR=blue]
    [Forms]![criteria_export_wo_tkt_records_dialog_form]![BeginDate][/color] 
And [COLOR=blue][Forms]![criteria_export_wo_tkt_records_dialog_form]![EndDate][/color] 

AND wo_activity_item = 'Temp_Alarm'

ORDER BY enter_date, enter_time;

You seem to have only two datetime parameters available so you probably need to create two more so that you can specify both time ranges in your SQL. I've indicated the places where you need additional data sources in blue.

 

[mlrmlr]

Hi Golom,

The query does not output the criteria dates.

 

[Golom]

>The query does not output the criteria dates

Not sure what you mean. In SQL lingo the "criteria dates" are the dates in the BETWEEN clause. i.e.

[Forms]![criteria_export_wo_tkt_records_dialog_form]![BeginDate]

and

[Forms]![criteria_export_wo_tkt_records_dialog_form]![EndDate]

Did you modify the second set (in blue) to some other source of date information? Your original query specified four date-time values that you wanted to use and, as I pointed out (as did PHV) your query has only two showing.

As to "... output ... the dates ..." do you mean display them or do you mean that it does not retrieve the correct data from the table?

Maybe this will be simpler

SELECT enter_date, enter_time, wo_activity_item, site_id, wo_comments

FROM wo_log_table

WHERE enter_date BETWEEN #03/10/06 16:00:00# 
                    AND #03/10/06 23:59:00#

AND wo_activity_item = 'WO System' 

UNION ALL

SELECT enter_date, enter_time, wo_activity_item, site_id, wo_comments

FROM wo_log_table

WHERE enter_date BETWEEN #03/11/06 00:00:00# 
                    AND #03/11/06 08:30:00#

AND wo_activity_item = 'Temp_Alarm'

ORDER BY enter_date, enter_time;

Now just supply four user input sources for these four hard-coded values.

Just another thought ...
I notice that you have both "enter_date" and "enter_time" fields. Does that mean that you have the date and the time in different fields? If so then you need something like

Where cDate(Int(enter_date) + TimeValue(Enter_Time)) BETWEEN ...

 

[mlrmlr]

Golom,

Thank you so much for your help. I'm almost there.
How can I replace the following Between [Forms]![criteria_export...] where you have a static date?

WHERE enter_date BETWEEN #03/10/06 16:00:00#
AND #03/10/06 23:59:00#


Between [Forms]![criteria_export_activity_records_dialog_form]![BeginDate] And [Forms]![criteria_export_activity_records_dialog_form]![EndDate]) AND...

 

[mlrmlr]

Golom,

I figured out the criteria issue, thank again.

Question, how can I perform the query and "Order by" on "Shift" but not export the data. On a non-Union query, you can drag the field to be queried and uncheck the field. The data will be filtered by the unchecked field just not displayed in the datasheet.

How can I get the same function in SQL?

 

[Golom]

Just add the field you want to the "Order By" clause at the end of the above SQL as in

ORDER BY [COLOR=red]wo_activity_item,[/color] enter_date, enter_time

where I've assumed that wo_activity_item is the shift field that you are talking about.