Pushing dates in production schedule

[OCStan]

I have a database which I use for scheduling production. I would like to know if there is a way to "push" the schedule dates. I have multiple products scheduled for production but at times I need to adjust the production dates by pushing all the schedule dates ahead by one day. I don't want to re-enter all the dates to do this adjustments.
Any help woukld be appreciated.

[RuralGuy]

I don't know your system structure but you can certainly adjust a date by one day with ease.

[OCStan]

I do my scheduling in a form called "Production Schedule" with a record source from a query called "Scheduling Query". The field names are job, structure#, structure type, schedule date, priorty.
The problem is the daily production schedule is filled out for two weeks and if we lose a day of production for some reason, snow storm, I need to move everything forward a day and skip the weekends or I may want to insert a job into the middle of the schedule.

[RuralGuy]

I've got to leave for a few hours but will check in when I get back. You could create a function that would update the field and skipping weekends is easy. How would you identify all of the records that need the schedule date bumped? I'll be back.

[Perceptus]

The high level aspect of your database do us nodda. What rural guy suggested is what you should do. How would you flag them is probally really simple. Every productionline usually has a way of referencing every part made.
If you had a column on your rows named recordflag. and the date field was in question. you could run this query and it would ask you for the date, where ever the flag had been set.
update tblProductionLine set mydatefield=[dateyouwant] where recordflagfield=true

[OCStan]

I would have a query with only the records that need to be moved. Sorry if this unclear, I'm new at this.

[RuralGuy]

I would have a query with only the records that need to be moved. Sorry if this unclear, I'm new at this.

Can you create a query that returns all of the records that need bumping? If so then turning it into an update query would be easy.

[OCStan]

Yes I can create the query for only the records to bump. Not sure how to do an update query.

[RuralGuy]

Next we need to create a Function in a Standard module that can adjust a date skipping weekends. I'll get to work on it in a bit.

[OCStan]

Thanks. I created an update query with "DateAdd("w",1,[scheduled])" in "update to" and it adds a day but it won't skip weekends.

[RuralGuy]

That's why we need a user defined Function. The Weekday(NewDate) needs to be < 6, 6 being Saturday.

[orange]

Thanks. I created an update query with "DateAdd("w",1,[scheduled])" in "update to" and it adds a day but it won't skip weekends.

I don't think your explanation is correct. I think this would add a 1 week.

Please follow RuralGuy's lead - he'll get you the answer.

[OCStan]

I switched it from "w" to "d".

[RuralGuy]

There is no built in function to skip weekends. We just have to build one.

[RuralGuy]

Try this Function in a Standard Module named basFunctions:

Function BumpDate(InDate As Date, BumpBy As Integer) As Date
' Adjust the InDate by the BumpBy value, skipping weekends
'If you want to bump by one then use:
'NewDate = BumpDate(OldDate, 1)
   Dim Looping As Boolean
   Looping = True
   BumpDate = InDate
   Do While Looping
      BumpDate = BumpDate + BumpBy
      Looping = Weekday(BumpDate, vbMonday) > 5
   Loop
End Function