asked on

Filtering Sat Sun from Microsoft Acccess

I have an issue. I am running a daily report using this to only get yesterdays numbers. >=Date()-1. But on Mondays I need fridays numbers cause there are no Sunday numbers. is there an easy way of filtering out Sat and Sun?

SELECT dbo_SOSO.WrittenBy, dbo_vwsnInvoiceLineSales.ExtendedPrice, dbo_ARInvoice.InvoiceDate
FROM (dbo_ARInvoice INNER JOIN dbo_SOSO ON dbo_ARInvoice.SONumber = dbo_SOSO.SONumber) INNER JOIN dbo_vwsnInvoiceLineSales ON dbo_ARInvoice.InvoiceNumber = dbo_vwsnInvoiceLineSales.InvoiceNumber
WHERE (((dbo_ARInvoice.InvoiceDate)>=Date()-1));

Open in new window

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gabe Lebron

ASKER

well it seem to work i will keep an eye on it. not sure what the 2,3,1,1,1,1, but it worked

Kevin Cross

It is like a switch/case statement. You are evaluating the first argument which is the week day of date() that results in values 1 - 7.

The next 7 arguments representing the value returned for each result of week day.

2 == on Sunday, subtract 2 days to Friday
3 == on Monday, subtract 3 days to Friday
1 == everything else (including Saturday), subtract 1 day to get to a week day.

Hope that makes sense.

Best regards,
Kevin

Kevin Cross

In case it wasn't clear, Sunday is week day 1 and Monday is week day 2 which is why it is 2, 3, 1, 1, 1, 1, 1 as it is going in order of the day of week numbers for Sunday - Saturday.