Update Query to change dates

[cjacobsen4]

I'm working on a report that highlights employees when they are leaving on travel and returning on travel and my problem is that the report is only run on the weekdays. It highlights all employees that returned yesterday so for instance on Monday's report it only shows people that returned Sunday and not Friday and Saturday.

My first thought was to make an update query convert those days to Sunday in a new column on my table which would then still cause the employee to be highlighted on monday. I made a table with all Fridays and Saturday's in the year and then in the second column is the Sunday Date to update the new field.

I haven't been able to get the update query to work correctly and was wondering if there would be a much easier solution so a person wouldn't continually extend the weekend table manually.

I really appreciate any help anyone has!

 

[Isskint]

Hi cjacobsen4

You could check for the day of the week using WEEKDAY() function and adjust the date range accordingly. In simple terms a formula like:
Iif(WEEKDAY(DATE,2)=1,>DATE - 3, =DATE-1)

 

[plog]

UPDATE queries are hardly ever the answer, they are band-aids. I don't believe you need that table of all Saturdays/Sundays either, just some logic. Isskin't provided what you need, use it in a SELECT query, not an UPDATE query.

 

[cjacobsen4]

Awesome! Yeah, I'm working the bugs out of it right now but I see how it should work.

Thanks!