Trying To Automate Daily Production Export To Excel

[Pete G]

Hi All;

I have a multi-query, multi-step process for daily production reporting that covers two shifts for 10 departments. I created a macro that will run steps 2-12, but have been manually keying the date parameters for the first step and was hoping I could automate it.

I have searched quite a bit to try and come up with an alternative, but haven't found anything. Here is the full query, including the date/time information in the where clause:

SELECT LEGO_TMSHT.JOB_NO_LOG AS JOB_NO, LEGO_TMSHT.OPCLASS_CODE_LOG AS OPCLASS_CODE, LEGO_TMSHT.CC_CODE_LOG, LEGO_TMSHT.TMSHT_PRODUCED_QTY AS Feet,
LEGO_TMSHT.TMSHT_WASTED_QTY AS Waste, LEGO_RULEIT.RULEIT_TEXT AS IIMP_FT, Round(LEGO_TMSHT.TMSHT_WASTED_QTY*IIMP_FT) AS WASTE_FT,
IIf(LEGO_RULEIT.RULEIT_VAL>0,((1/LEGO_RULEIT.RULEIT_VAL)*1000),'') AS Multiplier, [Feet]/1000 AS Total_Multi, Round(Total_Multi*Multiplier) AS Total_Lbs,
LEGO_TMSHT.TMSHT_CLOCK_IN, LEGO_TMSHT.TMSHT_CLOCK_OUT INTO tbl_24_Hr
FROM ((LEGO_RULEIT
INNER JOIN (LEGO_TMSHT
INNER JOIN LEGO_JOB
ON LEGO_TMSHT.JOB_NO_LOG = LEGO_JOB.JOB_NO)
ON LEGO_RULEIT.ITEM_CODE = LEGO_JOB.ITEM_CODE)
INNER JOIN LEGO_CC
ON LEGO_TMSHT.CC_CODE_LOG = LEGO_CC.CC_CODE)
INNER JOIN LEGO_JOBOP
ON LEGO_TMSHT.JOBOP_ID = LEGO_JOBOP.JOBOP_ID
WHERE (((LEGO_TMSHT.OPCLASS_CODE_LOG) In ('EXTRUDER2','EXTRUDING','LAMINATING','LAMINATOR2' ,'POUCHING','PRINTING','RECLOSE','SEAMING','SLITTI NG','ZIPPER'))
AND ((LEGO_TMSHT.TMSHT_CLOCK_IN) Between #3/31/2018 5:55:00# And #4/1/2018 6:05:00#)
AND ((LEGO_RULEIT.URULE_CODE)='IIMP_FT')
AND ((LEGO_CC.PLANT_CODE)='CP'))
ORDER BY LEGO_TMSHT.OPCLASS_CODE_LOG, LEGO_TMSHT.JOB_NO_LOG;



Barring doing something in VBA (I'm a novice, there), I can't think of a way to automate this.

Any ideas?

Thanks in advance!

Pete

[Micron]

Keying them in where?
You could have 2 date fields (textboxes) on your form for start and end dates and pass those values to this query. Some here don't seem to like such paramaterized queries (I'm not one) because they don't run properly if the form isn't open, plus you should verify that entries are dates before running the query. Otherwise it will still run with 'dog' and 'cat' in the 2 fields and not complain one bit.

Not sure what you mean by 'automating' either. You could be asking how to run this in the middle of the night when no one's around.

[June7]

How should the date range criteria be determined? The current date would be the end date? How are the times determined? You don't show AM/PM in the date/time.

In other words, what rules do you follow in making your determination of what date/time to use?

Other factors to consider:

Should the procedure be prevented from running more than once a day? Should the procedure run every day even if the db is not opened?

[Pete G]

Both times are AM. There is no form associated with this query. I would like to automate this query so that I can add it to the existing macro and then run all of them with a scheduled network task. The times are 5 minutes before one shift starts and 5 minutes after the next day's starts. NOT my criteria, but the way they've always done it LOL. The dates in the example represent the day before yesterday's production, as we run two 12 hour shifts.

[June7]

Not quite what I was looking for as a defined rule.

On any day the procedure is run, how should the date range be determined - BETWEEN Date() - 2 + #05:55:00 AM# AND Date() - 1 + #6:05:00 AM# ?

[Pete G]

Not quite what I was looking for as a defined rule.

On any day the procedure is run, how should the date range be determined - BETWEEN Date() - 2 + #05:55:00 AM# AND Date() - 1 + #6:05:00 AM# ?

#face palm# I figured it was going to be something simple. I didn't know you could do "+ #05:55:00 AM#" with the Date function. My original code for this was ">Date()-1 and <Date()" for a calendar 24 hour period and they said it has to cover the shifts.

Thanks a million!