• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

calculating business days

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
ajes01
Asked:
ajes01
  • 2
1 Solution
 
mlmccCommented:
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
 
Jeff TennessenAssistant Vice PresidentCommented:
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
 
ajes01Author Commented:
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
 
Jeff TennessenAssistant Vice PresidentCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now