Another removing weekends question

[catguy]

I have searched through the threads many times, but just can't seem to find what I am looking for. I have a query that returns only records that are "open" greater than 3 days, however, I need to remove the weekend days from this.
I would like to do this in an expression. This is what I currently have as an expression: Date()-[Qm15_Notifications_base]![Created on] and the criteria is > 3.
The "Created on" field is a date format (x/xx/xxxx).
I would think this should be somewhat easy, but am stumped.


Any help would be greatly appreciated.

[Rawb]

Assuming Qm15_Notifications_base is a Table name, the following should work for you. It checks to make sure [Created on] is at least 3 days in the past and that it isn't on the weekend (Sunday is the first day of the week and Saturday the 7th).

[catguy]

Thanks RAWB, I really appreciate your reply... but I wasn't very clear on my needs.
The expression you gave me does a great job of excluding [Created on] dates that would fall on a weekend day, but that's not what I need.

Looking at the "Created_on_Age" expression Date()-[Created on], it returns the number of days between today's date and the created on date.
If the created on date is 7/13/2010, and today's date is 7/29/2010, the returned value is "16" days. If the number of days that make up Saturdays and Sundays (non-working days, in this case "4") is removed from this equation, the results would then be "12" days.
I guess what I am trying to achieve then, is to reduce the "Total" number of days, by the number of "weekend" days that occur between the "Date()" value and the "[Created on]" value. So the result for "Created_On_Age" (or in the case of my query "Days open") would end up being the "number of working days"
I hope that made sense??

[Rawb]

I've spent all day thinking about this and have come up with the following answer: Whoever told you to do this is a sadist.

I've been able to generate a query that *approximates* the number you want, but my exhaustion-befuddled brain keeps trying to warn me that it's not going to always be accurate.

Maybe it can help you figure it out though!

Also, assuming I've managed to catch up on sleep by Monday, I'll take another look at this issue (yes, it's THAT interesting).

[catguy]

Rawb...... you are a GOD!!!!!!
I have been crushing my brain for the last 3 weeks trying to wring every last bit of info I can to satisfy the wants/needs/desires of my sadistic boss... all to no avail (actually, he just asked if I thought it could be done, and I of course said yes.... so maybe I'm the sadist?! I've been accused of it before)

I agree that it may not always be accurate, but I can monitor the data for awhile and see what happens.
For now... I am happier than a pig in....... a very happy place

Thanks so much for all the hard work... but if you find it THAT interesting.... what about holidays?! (kidding of course!!!!)

[Rawb]

*runs through the streets naked*

EUREKA! EUREKA!!!

Try this:

SELECT Qm15_Notifications_base.[Created on], IIf(DatePart("w",[Created on])=1,0,IIf(DatePart("w",[Created on])=7,5,DatePart("w",[Created on])-2)) AS Days_Diff, DatePart("ww",Date())-DatePart("ww",[Created on]) AS Weeks_Diff, [Weeks_Diff]*5+[Days_Diff] AS Age
FROM Qm15_Notifications_base;

I posted the SQL code instead of giving you screenies for two reasons: 1) Your fingers will thank me and, 2) The Query as shown in the Expression Builder was too long to show

Basically what it does though, is this: It counts each "full" week as 5 days. Then, for any "partial" weeks, it calculates Saturday and Sunday as the following Monday (so any entries from the weekend are counted as being made on the following Monday).

[catguy]

Works like a charm!

I hope your "run through the streets" was not quelled by any legal ramifications

Thanks for your diligence, I'm glad it was interesting to you.
Just one more small query to create, a little bit of code to write for automation, and this DB will be finished....... then on to the next.

Cheers