Solved

# Filtering Sat Sun from Microsoft Acccess

Posted on 2009-07-13
315 Views
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));
``````
0
Question by:gotti777
• 2

LVL 92

Accepted Solution

Patrick Matthews earned 500 total points
WHERE dbo_ARInvoice.InvoiceDate >= (Date() - Choose(Weekday(Date()), 2, 3, 1, 1, 1, 1, 1));
0

Author Closing Comment

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

LVL 59

Expert Comment

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

LVL 59

Expert Comment

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

## Featured Post

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.