Query Help (Weekdays)

[mattw]

Hi,

I've searched Google high-and-low trying to find my answer but haven't had any luck. I'm posting as a last resort hoping there's an easy answer to this and I can avoid doing this with multiple queries.

Everything I've done in Access has been self-taught, so please bare with me. The company I work for has a proprietary program written by a local programmer in Pervasive SQL. The daily users have a generic interface he built for data entry, etc. but, in order to get reports from the system, I have to use Access via ODBC links.

Ideally, I want my query to determine what today is (Mon-Fri) and pull the previous weekday, today and the next weekday's data. So...

Monday's report would pull the previous Fri, today(Mon) and Tues
Tuesday's report would pull Mon, today(Tues) and Wed
Friday's report would pull Thurs, today(Fri) and Mon

Unfortunately, no matter what I do, I can not get this to work. Based off the information I've pulled from my Google searches, I'm thinking it needs to look something like this...

IIf(Weekday(Date())=2,{If True},{If False})

It works fine if the True/False statements are single dates (ex: Date()+1, Date()-3, etc.) but when I try to define a range of dates (Between Date()-3 and Date()+1), it comes back blank.

Any thoughts on what I'm doing wrong? I've easily spent over 8 hours in the last week trying to figure this out. Most of the time I can find my answer by searching Google but this one has me stumped.



I appreciate any help you can provide. Thanks!

Matt

[ajetrumpet]

Ideally, I want my query to determine what today is (Mon-Fri) and pull the previous weekday, today and the next weekday's data.

try this:

BETWEEN 

iif(weekday(date()) = 1, dateadd("d", -3, date()), 

iif(weekday(date()) = 7, dateadd("d", -2, date()), 

dateadd("d", -1, date()))) AND

iif(weekday(date()) = 5, dateadd("d", 3, date()),

iif(weekday(date()) = 6, dateadd("d", 2, date()),

dateadd("d", 1, date())))

that even covers the possibility of you working on a weekend and having to pull a report on a saturday. =( Heaven for bid, lets hope not!

[weekend00]

1 Do you have tomorrow's data in today? (just curious)
2 how are you tables designed? Please provide table name, field name of DATE, etc.
3 post your query here so that we can see.
4 IIf(Weekday(Date())=2,{If True},{If False}): {IF True} and {If False} is values, not conditions. Weekday(Date())=2 is the condition.

[mattw]

Adam,

Your code is VERY close... and way closer than I got on my own! Thanks for the quick response! The only thing is I don't want it to pull weekend data. If it's a Monday, I need it to pull Fri/Mon/Tue; if it's a Fri, I need it to pull Thurs/Fri/Mon. There is data on the weekend but I don't want to include it for this query (but it will come in handy for another query I need to do in the next couple of weeks).

Any minor tweak to have it exclude the weekend data? Thanks!

Matt

[mattw]

Weekend00,

1. Yes, the data is in already. It's real-time and updated frequently throughout the day. There's about 2 years of past information and 2+ years of future information available. Good question and one I should have mentioned in my original post.

2. They're not technically tables in Access, they are ODBC links to the Pervasive SQL server. Really, though, they are treated similar to a table. The field names are basic: JOB_NO, MACHINE, DATE1, SCHEDTIME, etc.

3. Sorry, I can't figure out how to post a screen capture of the query. Do I need to post it on a free site and link to the image?

4. Yes, True/False would be values, not conditions. Sorry if the way I worded that sounded confusing.

I appreciate your help. I think Adam's solution is really close. Hopefully a minor tweak can get me what I need.

Matt

[weekend00]

Matt:

Thanks for reponding my post even when you almost have your answer.

maybe you can also try this(in my computer's setting, weekday(sunday)=1):
where date1=date() or
date1=iif(weekday(date()) =2 ,date()-3,date()-1))) or
date1=iif(weekday(date()) =6 ,date()+3,date()+1)))

[mattw]

Weekend00,

Keep in mind, I'm not that good with Access, so I apologize in advance if my response sounds ignorant.

When I copy/paste in your code (everything from "where..." on) into the criteria section for my Date1 column, I get "The expression you entered has too many closing parentheses.". Is it as simple as removing the extra closing parentheses... or should there be some other open parentheses somewhere? Am I pasting the formula in the wrong place? I did try removing the closing parentheses, but then other weird things happen. I'll try to explain them better in my next post, if you confirm that I'm not missing some open parentheses somewhere.

Thanks for your continued help.

Matt

[weekend00]

please paste following to criteria cell:
date() or iif(weekday(date()) =2 ,date()-3,date()-1) or iif(weekday(date()) =6 ,date()+3,date()+1)

[mattw]

Weekend00,

Perfect! That did exactly what I needed! I changed my computer's date to Monday and Friday to test it and it's working exactly as I hoped it would. Thank you for sticking with me on this and walking me through the exact steps to make it work. I'm certainly more knowledgeable in Access than I used to be but have a LONG way to go before I feel comfortable in it.

Now to take your formula and figure out exactly what all of it means. That's the only way I actually learn.

Matt

[weekend00]

have a good day.

[mattw]

Weekend00,

Just wanted to let you know the light bulb clicked on and the simplicity of your formula now makes complete sense. I was stuck on trying to get it to pull a "range of dates" rather than pulling individual dates in the range I needed. Thanks, again!

Matt