Manufacturing Scheduling Process - Task is to assign a date/time (as a starting point) to operations, starting with the Ship Date and backing up 1 day for each operation, skipping weekends and holidays. The time to be at 7:00 AM.

My 1st stage was to assign the date, leaving the time alone. That was successful. (It was nice working on this piece this week with Good Friday and the weekend to use for testing). The dates are coming out as expected (although I wish it would display 2 digit hours, the format in the table is mm/dd/yy hh:nn - I try to enter Hh:nn and it converts back to hh:nn)
Op 1 04/05/12 0:00
Op 2 04/09/12 0:00
Op 3 04/10/12 0:00
Op 4 04/11/12 0:00

The Ship Date is 04/12/12 0:00.

When I attempt to 'add' 7:00 AM the results still show a time of 0:00. I know the Ship Date values all have a time portion = 00:00:00.000 displays when I execute a SQL statement. Here's the code. The value of dtmOpStartDate is set to the Ship Date prior to looping through each operation. The function fCalcBusDate calculates business days, -1 to subtract 1 day.:


Using Access 2007.

I cannot figure out the problem.

This post has been edited by edaroc: Apr 4 2012, 02:19 PM

Hi Ed,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Try changing your TimeSerial() code to:

...+ TimeSerial(7,0,0)

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Well, DbGuy, your 2 cents are worth a mint! Thanks!
That solved the problem.

Not understanding exactly why the code I copied from an online 'solution' wasn't working I went to TechOnTheNet.com's Access Functions webpage and looked at the information there and saw that the function argument format is as you indicated. I wonder where the format of the code I copied came from. Oh, well. I'm happy and moving on.

Hi Ed,

(IMG:style_emoticons/default/yw.gif)

Glad to hear it worked for you. Good luck with your project.