DateAdd Functions
I am trying to perform some date due functions, however have an issue with it. I am performing a DateAddW expression, however...I need to account for Sunday's and Saturdays. See table below.
If your data is in ACCESS then I would expect that you are going to have to use weekday or some technique to check for the day the result gives and adjust accordingly. Probably better in a function.
As I told you you need to check on the day of the result, if I have to do all the work it would be
duedate: IIf([account]="PSG",IIf(Weekday(DateAdd("w",5,[fdate]))=7,DateAdd("w",7,[fdate]),IIf(Weekday(DateAdd("w",5,[fdate]))=1,DateAdd("w",6,[fdate]),DateAdd("w",5,[fdate]))))
I used fdate as I couldn't remember your field name.
Note
ITs Dateadd not Dateaddw
The number of days being numeric is not in quotes
A more carefull read of help on Dateadd is called for, also read help on weekday to ensure that you understan what is going on.
1) Imported the spreadsheet to tblTat.
2) Added Account field
3) Renamed other fields DayOfWeek, TAT, RecDte, DueDte. Note that Date is a reserved word in Access and should not be used as a field name. Spaces in field names are not only a pain, but inhibit some internal Access functions (e.g. Intellisense)
3) Hand input PSG, ESS to [Account] as appropriate
4) Deleted unnecessary rows
The date fields (RecDte, DueDte) imported as strings containing the double numbers (e.g. "39908" as stored by Access. To convert them to dates, added 2 datefields (RecDteX, DueDteX) and ran an Update query where the Update To looked like:
CDate(Int(CDbl([RecDte]))) and CDate(Int(CDbl([DueDte])))
Deleted fields RecDte and DueDte, then renamed fields RecDteX and DueDtex
The following function adds (or subtracts) business days (Mon - Fri) to a date. It doesn't, however, mimic the logic of using the next Monday as the start date if the original start date is a Saturday or Sunday.
Code:
Function UpBusDays3(pstart As Date, _
pNum As Integer, _
Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose: Add or subtract business days
' from a date
'Coded by: raskew
'Inputs: 1) +) ? UpBusDays3(#2/17/06#, 3, True)
' 2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output: 1) +) 2/22/06
' 2) -) 2/17/06
'*******************************************
Dim dteHold As Date
Dim i As Integer
Dim n As Integer
dteHold = pstart
n = pNum
For i = 1 To n
If pAdd Then 'add days
dteHold = dteHold + IIf(WeekDay(dteHold) > 5, 9 - WeekDay(dteHold), 1)
Else 'subtract days
dteHold = dteHold - IIf(WeekDay(dteHold) < 3, Choose(WeekDay(dteHold), 2, 3), 1)
End If
Next i
UpBusDays3 = dteHold
End Function
...so, created an Update query that computed the due date as:
this statement: InStr("17",Weekday([RecDte])) adds 1 day if RecDte is Sunday, 2 days if RecDte is Saturday, otherwise 0.
this statement: IIf([Account]="PSG",5,4) returns the number of business days to add. If there were more than 2 Accounts, this would have to be modified, probably using the Switch() function rather than Iif().
In testing, the resulting dates corresponded completely with your DueDte.
Bob
I had to look up the Ozarks , it is not part of the US I have visited, looks nice, why are you bored?
The guy already has a DB the EXCEL was a red herring.
If there is more than one account to allow for with different "adds" I would use a function using Select Case and basing the alterations on the date recieved, but he signed off last night, my time, without responding to my suggestion, so not sure were we stand at the moment.
