Identifying Weekend Dates

[MarketAn]

I am trying to write a query that will take a date and add an additional day, but fridays I need to add 2 days. Is there any way to identify weekends?

Thanks!

 

[scriverb]

Try this in a query. Change your table name in the query but it adds one day for all days of the week except Friday and adds two for Fridays.

UPDATE Table1 SET Table1.DateField = IIf(DatePart("w",[Table1]![DateField])=6,DateAdd("d",2,[Table1]![DateField]),DateAdd("d",1,[Table1]![DateField]));

Good luck

Bob Scriver

 

[MichaelRed]

I'm pretty sure that the datepart arg "w" is for week, and that the correct value here is "d".

IIF (WeekDay([DateField]) = vbFriday, [DateField] = [DateField] + 2, [DateField] = [DateField] + 1)

should give the results stated (although I'm not sure that what is sated is what is wanted? This appears to be ye olde question re "What is the next business day?" - disregarding the nicieties of holidays, and getting the math somewhat askew for the weekend days to skip.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[scriverb]

MichaelRed: No the "w" parameter value stands for "day of week". The parameters "ww" would return the week that the date appears in the year(value 1 - 52). The "day of the week" is a value from 1 - 7 with the default of Sunday being 1. You can change the beginning day of the week with the optional third parameter of the DatePart Function to adjust the beginning of the counting. Please see ACCESS Help for clarification on how this works.

The query that I provided does in fact perform the function that MarketAn requested. DatePart("w",[Table1]![DateField]) returns a value of 6 for Friday. So, 2 days are added. All others have 1 day added.

Lastly the requestor asked for a query not just an IIF statement so I believe my original response provides the requested assistance.

I hope this helps you understand my response to this question.

Bob Scriver

 

[raskew]

UPDATE tblHolidates SET table1.theDate = [theDate]+IIf(Weekday([theDate])=6,2,1);