Working days calculation on form

[Steve_Bache]

Anyone help please ? I'm still fairly new to ACCESS so forgive me if I'm completely on the wrong track !

I wish to add a control button to a form that will prompt me to enter a date and then calculate and display a date nn (fixed) number of WORKING days in the future.

I've seen some functions suggested in the forum and as I'm unfamiliar with this topic, I have tried putting together some code (see below) related to an On Click event for the button. I'm having syntax problems and have been battling away at this for some time without success.

Suggestions please !

Steve



Private Sub cmdDate_Click()
Dim FirstDate As Date ' Declare variables.
Dim Number As Integer
Dim dtEndDay As Date
Dim dtinterimday As Date
Dim intcount As Integer
Dim lnginterimdate As Long
Dim intdays As Integer

Dim Msg
FirstDate = InputBox("Enter a date - dd/mm/yy")
Number = 20

dtinterimday = FirstDate
intdays = Number
Do Until intcount = Abs(intdays)

If intdays > 0 Then dtinterimday = dtinterimday + 1 Else dtinterimday = dtinterimday - 1

lnginterimdate = dtinterimday

If WeekDay(dtinterimday, 2) <> 6 And WeekDay(dtinterimday, 2) <> 7 Then

intcount = intcount + 1
End If
End If

Loop


Msg = "New date: " & dtinterimday
MsgBox Msg
End Sub

 

[Malcy]

Try doing a search on working days and I am pretty certain when I did it a few months back I came up with some perfect illustrations.
No point in reinventing the wheel!!

 

[Steve_Bache]

Found It!

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=41006&highlight=weekdays


Malcy
Had already spent some time going through previous threads. However, took your advice and looked again and lo' and behold , eventually found what I needed - see above.

Thanks for the kick up the butt !

Steve

 

[RuralGuy]

Here's a web site to bookmark until it goes away.
A plethora of date functions

 

[Malcy]

Hi Steve
No kick up butt was intended. Rather I knew there was something if you searched on those terms since had been there already!
Glad you found a solution.
I have to say you can find a solution for almost everything (well everything Access based) here.

I also liked RuralGuy's link. Quite a nifty little resource!
Best wishes

 

[Steve_Bache]

Cheers

Thanks guys. It's great for us newbies to know there is someone out there who's willing to take the time and trouble to help.

Cheers