Troubleshoot Weekending Expression in Query (1 Viewer)

[MrMitchell]

Hey all I have the following expression built in a query:

weekend: DateAdd("d",-Weekday([scheduled_date])+6,([scheduled_date]))

. My reporting week is Saturday-Friday, and the output date is perfect (i.e. weekending date is 6/1/2012, 6/8/2012, etc.) however, it is still including saturdays as part of the previous week (meaning 6/2/2012 is being reported as WE 6/1/2012). Can't figure this one out, any help would be greatly appreciated!

Thanks in advance for your time!

 

[datAdrenaline]

It would be helpful for us to see what the SQL View (your SQL Statement) of your Query object is. But even without that, your expression does not return the correct value. Please give the following expression a go ...

DateAdd("d",7-Weekday([scheduled_date], 7),[scheduled_date])

The 2nd argument ("week begin") of 7 in the Weekday() function is the value of the constant vbSaturday.

Example from the immediate window (using the named const, instead of the literal value)

? DateAdd("d",7-Weekday(#6/2/2012#,vbSaturday),#6/2/2012#)
6/8/2012

Hope that helps!