Solved

Filtering Sat Sun from Microsoft Acccess

Posted on 2009-07-13
4
315 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:gotti777
  • 2
4 Comments
 
LVL 92

Accepted Solution

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

Author Closing Comment

by:gotti777
Comment Utility
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

by:Kevin Cross
Comment Utility
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

by:Kevin Cross
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now