Solved Reporting data from last working day in Access

Microsoft Office 2010 professional
July 29, 2013 at 11:21:12
Specs: Windows XP
I created a query to get yesterday's data from my database and it works fine for everyday except Monday. The criteria is specifically set up to find the accounts that came in yesterday so it reads Date()-1.

Is there any function in Access that already accounts for weekends or what would I have to use as criteria to get Friday's information on Monday?


See More: Reporting data from last working day in Access

Report •

✔ Best Answer
July 29, 2013 at 20:07:45
GingerLeake,

Your iif statement looks good to me; I wouldn't worry about changing anything. Just as an FYI tho, there are other ways of doing it, for instance:

You can create a calculated field in your query using the info in the link below
http://office.microsoft.com/en-us/a...

http://www.techonthenet.com/access/...

Then put a parameter in your Criteria line for that new calculated field. You can also have the query prompt the user for criteria by using brackets [] in the criteria field. for Instance: [Please enter Value] will prompt the user for a value and use it as part of the query before it runs.

I hope this information helps!

Law of Logical Argument: Anything is possible if you don't know what you're talking about.



#1
July 29, 2013 at 12:38:22
There aren't too many Access users in this forum, so you might want to ask your question in an Access specific forum.

I did come across the following link via a Goolgle search, but since I don't use ACCESS, I don't know if the suggestions in the thread can be structured for your situation.

http://www.access-programmers.co.uk...

Basically it explains how to eliminate weekend days from a query related to finding the number of days between 2 dates. So I'm thinking that maybe you can do a calculation where instead of Date()-1 you would use something like Date() - 1 Working Day.

I may be totally off base with that suggestion, but AFAICT you are not the only one troubled by this "weekend" thing in Access. Maybe the answer can be found via a carefully structured Google search.

Good Luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 29, 2013 at 13:03:24
Thanks, DerbyDad! I know there aren't many of us here but I do have a buddy who watches for me so if I don't get a response in a day or two, I still know it will come in.

And thanks for the link! I'd come across that site too but wasn't able to find what I was looking for and Google and Bing searches were less than helpful.

I did find something which seems to be working but I'll have to run it the next few days to find out for sure. It's an IIF statement, and yes, that's if with two i's. ~~iif(format(date(),"ddd")="Mon",date()-3,date()-1)

Like I said, I don't know if it's really going to work but it did for today.


Report •

#3
July 29, 2013 at 13:13:07
I don't know what it will do to your database, but when I need to test a date based function in Excel, I change the system date so Excel thinks it's a different day/month/year. That way I can test something that is only supposed to work on Monday's or the first time a workbook is opened each week or some other date based occurrence.

I do the same thing for time based functions by changing the system time.

I don't know if you are able to test your iif statement by making the application think it's Monday/Tuesday/etc. but that might help you find a solution sooner. I'd hate to think that you can only test a given function once a week. I'd go crazy!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 29, 2013 at 13:22:21
Well now clearly I am not as thorough as you....

I'll test it tomorrow morning (this is a daily report) and the following morning--manipulating the dates so I can give the reports to management if the iif statement doesn't work--but consider this solved if all three days are good.


Report •

#5
July 29, 2013 at 20:07:45
✔ Best Answer
GingerLeake,

Your iif statement looks good to me; I wouldn't worry about changing anything. Just as an FYI tho, there are other ways of doing it, for instance:

You can create a calculated field in your query using the info in the link below
http://office.microsoft.com/en-us/a...

http://www.techonthenet.com/access/...

Then put a parameter in your Criteria line for that new calculated field. You can also have the query prompt the user for criteria by using brackets [] in the criteria field. for Instance: [Please enter Value] will prompt the user for a value and use it as part of the query before it runs.

I hope this information helps!

Law of Logical Argument: Anything is possible if you don't know what you're talking about.


Report •

Ask Question