how to filter weekends

[pranvera]

Hello guys,

I am new user with access 2007 and I happy if you would help me with one problem I am not able to sort out.

I need make query to filter daily delivered loads which are not older than 3 days. For this I am trying to filter in table delivered loads by funcion <date()-3 . However, I have problem with weekends because I need to skip them.

For example: on Monday I need to start count loads from last friday (monday I will see loads from sunday, saturday,friday,thursday,wednesday.), On Tuesday I need to see loads for monday, sunday,saturday,friday and thursday. On Friday I need to see loads from Thursday,Wednesday and Thuesday.

I tried something like :


WHERE (((IIf(Weekday(Date())=3,(Date()-[Req Delivery Dt/Tm])<=5,(Date()-[Req Delivery Dt/Tm])<=3))<>False));

Thank you for any idea.

[NTC]

I don't believe the issue is weekends per se - because they always occur in the same place relative to your starting day. I think the issue is determing the correct formula.

for each potential day determine the correct formula to identify all appropriate ship days via DateAdd method

then string those formulas together with an IIF i.e. if start date is Monday then use Monday formula, if start date is Tuesday use Tuesday formula... ( obviously you could use If/Then or Case method if in vb)

from your post it wasn't clear that as to whether or not each day had a differing formula or not - it seemed like they might, it was difficult to understand your post. Probably so because the weekend days are mathmatically different if one starts from a Monday vs a Wed......But in any case it doesn't matter. The concept is the same.

Hope this helps a little.

[weekend00]

if you don't count holidays, you can use following formula to skip Saturday and Sunday:

>= iif(datepart("w",date) <=4 ,date-5,date-3)

[ajetrumpet]

Hello guys,

I am new user with access 2007 and I happy if you would help me with one problem I am not able to sort out.

I need make query to filter daily delivered loads which are not older than 3 days. For this I am trying to filter in table delivered loads by funcion <date()-3 . However, I have problem with weekends because I need to skip them.

For example: on Monday I need to start count loads from last friday (monday I will see loads from sunday, saturday,friday,thursday,wednesday.), On Tuesday I need to see loads for monday, sunday,saturday,friday and thursday. On Friday I need to see loads from Thursday,Wednesday and Thuesday.

I tried something like :
WHERE (((IIf(Weekday(Date())=3,(Date()-[Req Delivery Dt/Tm])<=5,(Date()-[Req Delivery Dt/Tm])<=3))<>False));

Thank you for any idea.

pranvera,

it may just be as simple as this as well:

WHERE [datefield] > dateadd("d", -4, date()) AND

weekday([datefield]) BETWEEN 1 and 5

[slave138]

I'll go one even easier:

WHERE [datefield] <= dateAdd("w", -3, Date())

[ajetrumpet]

I'll go one even easier:

he wants newer than 3 days, not older.

[slave138]

Read his description again. He wants the deliveries from the last 3 weekdays.

EDIT: My apologies - you were right. It should be:

WHERE [datefield] >= dateAdd("w", -3, Date())

[weekend00]

he wants 5 days if now is Monday, Tuesday, or Wednesday.

For example: on Monday I need to start count loads from last friday (monday I will see loads from sunday, saturday,friday,thursday,wednesday.), On Tuesday I need to see loads for monday, sunday,saturday,friday and thursday. On Friday I need to see loads from Thursday,Wednesday and Thuesday.

[slave138]

he wants 5 days if now is Monday, Tuesday, or Wednesday.

That means he wants to see everything going back 3 week days. If there is a weekend in the middle, it will come out to 5 days but all that really matters is that it goes back 3 week days.

[pranvera]

WHERE [datefield] <= dateAdd("w", -3, Date())

thank you guys for all answers.

When I try use formula: WHERE [datefield] <= dateAdd("w", -3, Date()) query has problem with syntax and always trys to marked coma between w and -3.

I wanna apologize for my poor english and desribtion of my problem.
Yes, I need to query shows always 3 last days, If there is a weekend in the middle need to see 5 last days.

[slave138]

If you wouldn't mind uploading a sample it would help figure out what is missing. All it would need to include is a blank copy of the tables and the query.

[weekend00]

dateAdd("w", -3, Date()) is same as dateAdd("y",-3,date()) or dateAdd("d", -3, Date()) , it doesn't skip weekend.

we still need IIF

[slave138]

dateAdd("w", -3, Date()) is same as dateAdd("y",-3,date()) or dateAdd("d", -3, Date()) , it doesn't skip weekend.

we still need IIF

My apologies. I was confused by what they meant by "weekday" thinking they were using the term the same way they use it in Outlook instead of referring to the day number within the week.

I would agree that your example would probably be the shortest way to do it.

>= iif(datepart("w",date) <=4 ,date-5,date-3)

[pranvera]

I would agree that your example would probably be the shortest way to do it.

>= iif(datepart("w",date) <=4 ,date-5,date-3)

I see. Trying to use this formula query asks for date parameters. Am I doing anything wrong if my SQL looks like :

SELECT MarDaily.*
FROM MarDaily
WHERE (((MarDaily.[Req Delivery Dt/Tm])>= iif(datepart("w",date) <=4 ,date-5,date-3)));

[slave138]

Try:

SELECT MarDaily.*
FROM MarDaily
WHERE (((MarDaily.[Req Delivery Dt/Tm])>= iif(datepart("w",date()) <=4 ,date()-5,date()-3)));