Less 3 business day

[ddtpmyra]

Hello,

I have this query that has log-on date, and below is my sample query. My problem right now is the user wants to skip the weekends when counting backwards. I know there should be a way but seriously I'm starting pulling my hair here. Please help.

SELECT Name, [LastLog]from tablename
WHERE [Last Log On]<=now()-Weekday(3);

example:
- Now() is equal May 2, 2011
- My query pulls all records happens from April 29 backwards.
- My user wants to start the counting from April 27 instead because they want to count the weekends.

I hope I explain this very well. Thanks!

Or how can I do the mathematical calculation of the now()-3 business day before passing it to the next query?

[NeoPa]

You could try :

SELECT [Name]
     , [LastLog]
FROM   [TableName]
WHERE  ([Last Log On]<(Date()-3-IIf(Weekday(Date(),vbSaturday)<6,2,0))

Never, ever, use Now() for date calculations though. It is a Date and Time value and this means that it won't work if used as a simple date.

[ddtpmyra]

I'm getting this error when I paste it the function vbSaturday converted into string like the code below:

SELECT status.[Last Log On]
FROM status
WHERE ((([Last Log On]<(Date()-3-IIf(Weekday(Date(),"vbSaturday")<6,2,0)))));

[colintis]

What sort of error you are getting without putting the double quotes aroudn vbSaturday in the query?

[NeoPa]

That was silly of me (DOH!).

Please replace (the VBA Intrinsic Constant) vbSaturday with its value which is 7. It's good practice to use it in VBA, but really quite stupid to try to use it in SQL (where it cannot be recognised). I feel appropriately dumb ;-(

[ddtpmyra]

No your not, I do am the one asking questions here (smile). Thanks for all of your help.

[NeoPa]

You're welcome.

I know I'm not dumb, but sometimes it feels that way when I let something silly like that slip out. Thank you anyway :-)