Past Work Date

[usmcret]

I have a Field called [MfgTime] I want to subtract this number from [OrigShDate]. example
[OrigShdate]- [MfgTime] = [OrigJGDate]
08/01/06 - 3 (days) = 08/27/06
This should exclued WeekEnds.
I have found all kinds of formulas that add to the date but none to subtract.

Thank you

 

[rjoubert]

Use the DateAdd function, but use a negative number.

DateAdd("d", -3, [OrigShdate])

 

[Golom]

Excluding weekends is a bit tougher. I assume that you mean that you want to subtract 3 work days so
#07/31/2006# (Monday) - 3 Work Days = #07/26/2006# (Wednesday) and NOT #07/28/2006# (Friday)

DaysSubtracted = 0
DaysToSubtract = 3
TheDate = #07/31/2006#
Do Until DaysSubtracted = DaysToSubtract
   If WeekDay(TheDate) >= 2 AND WeekDay(TheDate) <= 6 Then
      DaysSubtracted = DaysSubtracted + 1
   End IF   
   TheDate = TheDate - 1
Loop

 

[usmcret]

rjoubert
I see were you are going but can DateAdd be written as DateADD("d",[MfgTime],[OrigShDate])?
==================
Golom
In your Code can TheDate = [MfgTime]?
I need for the qty in [MfgTime] to be subtraced from [OrigShDate] Since each product has a different number of day for manufactoring?

Thanks in advance for the help. Access is great (you can call it job securety) but sometimes I just want to jump on my computer.

 

[rjoubert]

No the DateAdd function takes an integer for that second parameter. It returns a date that results from adding the interval (2nd parameter) to the date passed in as the 3rd parameter. If you want to find the difference between your two date fields, you can try [MfgTime] - [OrigShDate], or check out the DateDiff function...

DateDiff("d", [MfgTime], [OrigShDate])