A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Due Dates That Fall on Weekend



 
 
Thread Tools Display Modes
  #1  
Old May 5th, 2010, 04:56 PM posted to microsoft.public.access.reports
Julie Gilliam[_2_]
external usenet poster
 
Posts: 8
Default Due Dates That Fall on Weekend

Can someone please tell me what I need to do to make any due date that falls
on a Saturday or Sunday default back to Friday.

I need very specific instructions as I have tried to do this for several
years and can not get it to work properly.

Thanks Julie
  #2  
Old May 5th, 2010, 06:43 PM posted to microsoft.public.access.reports
ghetto_banjo
external usenet poster
 
Posts: 325
Default Due Dates That Fall on Weekend

For a specific answer, we might need some more details. Is a user
entering a date on a form that needs to be verified at that time? Is
the due date being calculated based on a different date?

It won't be too hard to do though. You can use the Weekday() function
and the DateAdd() function. Post a few more details and we'll help ya
out.

You can use logic like this:

if Weekday([duedate]) = 7 then
[duedate] = DateAdd("d", -1, [duedate]
elseif Weekday([duedate]) = 1 then
[duedate] = DateAdd("d", -2, [duedate]
end if



  #3  
Old May 5th, 2010, 07:07 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Due Dates That Fall on Weekend

An expression like the following should work to calculate the desired date.

IIF(Weekday([SomeDate],2)5,DateAdd("d",5-Weekday([SomeDate],2),[SomeDate]),[SomeDate])

That will work in a query as a calculated field. Elsewhere it may fail if
SomeDate is ever null.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

ghetto_banjo wrote:
For a specific answer, we might need some more details. Is a user
entering a date on a form that needs to be verified at that time? Is
the due date being calculated based on a different date?

It won't be too hard to do though. You can use the Weekday() function
and the DateAdd() function. Post a few more details and we'll help ya
out.

You can use logic like this:

if Weekday([duedate]) = 7 then
[duedate] = DateAdd("d", -1, [duedate]
elseif Weekday([duedate]) = 1 then
[duedate] = DateAdd("d", -2, [duedate]
end if



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.