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

gotti777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
WHERE dbo_ARInvoice.InvoiceDate >= (Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gotti777Author Commented:
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
0
Kevin CrossChief Technology OfficerCommented:
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
0
Kevin CrossChief Technology OfficerCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.