?
Solved

calculating business days

Posted on 2003-03-29
4
Medium Priority
?
292 Views
Last Modified: 2010-04-17
I have an Access 2000 form DueDate field that I want to default to Date() + 3 days.  If the third day (due date) falls on Saturday or Sunday then I'd like the date to roll to Monday.  I'm new to vb/vba.

Thank You in advance.  
0
Comment
Question by:ajes01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 8231729
You can use Format([DueDate],"ddd",1) returns 3 character string for the day of the week.

if (Format(DateAdd([DueDate],3),"ddd",1) = "SAT") then
  DateAdd([DueDate],5)
else if (Format(DateAdd([DueDate],3),"ddd",1) = "SUN") then
  DateAdd([DueDate],4)
else
  DateAdd([DueDate],3)
endif

Use something like that

mlmcc
0
 
LVL 8

Accepted Solution

by:
Jeff Tennessen earned 200 total points
ID: 8236800
If by "default to Date() + 3 days" you mean that you would like the value of the DueDate field to be Date() + 3 when a new record is created, enter the line below into the Default Value property for that field:

IIf(Weekday(Date()+3,7)<3,Date()+6-Weekday(Date()+3,7),Date()+3)

You could also create a custom function with similar logic:

Public Function DatePlusThree() As Date
  If Weekday(Date + 3, vbSaturday) < 3 Then
    DatePlusThree = Date + 6 - Weekday(Date + 3, vbSaturday)
  Else
    DatePlusThree = Date + 3
  End If
End Function

Then enter the function name (DatePlusThree() in this example) into the Default Value property. This could be faster because it does not have to calculate both the "true" and "false" expressions. It isn't likely this would be an issue, but if large numbers of rows are entered into the table in very short time spans, it's conceivable that it could have an impact.

Jeff
0
 

Author Comment

by:ajes01
ID: 8269190
The function works quite well.  I could not get the default value property to work though.  However I inserted it at the table level for the field and while I'm typing this I'm thinking you meant the defualt value for the object on the form.  I will try that.  Can you recommend a good book/source of info that covers functions?  I looked in the help file and the example lacked explanation.

Thanks!
0
 
LVL 8

Expert Comment

by:Jeff Tennessen
ID: 8281640
Actually, what I was meant *was* the Default Value property for the field at the table level. It would also work in a text box's Default Value property if you would rather specify when that field exhibited this default behavior, but it sounds like you've got it where you need it to be.

The best books on Access programming I've ever seen are the Access Developer's Handbook series by Ken Getz, Paul Litwin, et al. The Access 2002 edition has both a Desktop and an Enterprise flavor. I'd recommend the Desktop to start out with. You can find out more at: http://www.developershandbook.com/.

Jeff
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

800 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