Date calculation trouble.

[dixxy]

Hello,

I am having trouble achiving my end result.

I have a form (single) with a subform (cont.) and would like to be able to cpount the number of wokdays beteewn now() and a date that is in the table (or in the form, same one)...this is the code I am trying, but it returns 0

On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

right now i have put this in the click event of a button to test it.

Me.WeekDays = intCount

what am i doing wrong?

I know there are many examples of code in this forum, but it always gives me errors, and not being a VBA gouru, i have great difficulty fixing it...maybe i don't implement it right, I'm not 100% sure..

Any help, would be appriciated.

Thanks in adavanced,

Thanks,

Sylvain

 

[PHV]

Probably something like this ?
Me.WeekDays = WorkingDays([some date field])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixxy]

tahnks for the fast reply,....but which [some date field].. and when i put that code in i get an error saying 'Expected variable or procedure, not module'

???

Thanks,

Sylvain

 

[spizotfl]

it looks like WorkingDays is a function, correct? does it have an argument that accepts a StartDate? i would guess that you are getting a zero because no conditions in the case statement are being met. i think PHV is suggesting that you make sure you are passing the function a date to work with.

 

[dixxy]

spizotfl

Can you elaborate on this a little more...yes the function is WorkingDays. What argument with StartDate..sorry to make you go into some much details...

Thanks,

Sylvain

 

[spizotfl]

you have the following:

StartDate = StartDate + 1
....

intCount = 0
Do While StartDate <= EndDate

if there is a problem with the start date/end date, the loop never runs, so intCount is 0.
where does start date come from? check to make sure that when you call the function you are passing a valid date value. also it looks like you are probably passing an end date, so make sure that it is also a valid date value, and after the start date.

 

[dixxy]

i forgot par of the code...here is the entire thing...

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDays
' Inputs:   StartDate As Date
'           EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

the start date comes from the form.
What do you mean 'passing a valid date value'?

Thanks,

Sylvain

 

[PHV]

'Expected variable or procedure, not module'
Seems you named the module WorkingDays too.
You can't have a module and a function with the same name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixxy]

PHV,

yes i had the name of the module the same as the name of the function, now i have it different, the module is called 'days'

this is what i have in the click of a button to make this happened;

Me.WeekDays.Value = WorkingDays(Me.EndDate - Me.StartDate)

this give me a compile error 'argument not optional'

??

Thanks,

Sylvain

 

[spizotfl]

The function says you call WorkingDays(StartDate, EndDate):

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer

You have WorkingDays(Me.EndDate - Me.StartDate):

Me.WeekDays.Value = WorkingDays(Me.EndDate - Me.StartDate)

You need WorkingDays(Me.StartDate, Me.EndDate)

 

[dixxy]

WOW, WOW, WOW, after banging his head on the wall all morning now he i start to see the light!!!

Thanks guys,

but the worst is not over yet,now if i put this code on the form current event, it will do the first record in the recorset but not the other ones. How to i get it to go throught every record to calculate this?

this is what i put in the form current;

Me.days = WorkingDays(Me.StartDate, Me.EndDate)

Thanks,

Sylvain

 

[spizotfl]

is this a continuous form?

 

[dixxy]

it's a single with a continuous for inside of it.

Thanks,

Sylvain

 

[spizotfl]

where are you trying to show these calculations, on the single part, or on the continuous subform?

 

[dixxy]

on the continuous subform


Thanks,

Sylvain

 

[spizotfl]

ok, that moves a step beyond where i am useful. i am not sure how to make that work, though hopefully someone in the know will let us both know.

 

[PHV]

Make the calculation in the subform's underlaying query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[spizotfl]

so you're saying put the function call in the query of the recordsource for the subform? that makes very good sense....

 

[dixxy]

ok back to this,

it works to call the function in the query (or the form's SQL in this case), now the problem is that i would like it to to this calc. based on the start date and today.

i have modified the code above with this

Do While StartDate <= Now()
[\code]
but it does not work, i get '#error' in the field on the form.
if there is 2 date in the table it does fine, but i would like it to do from the start data to today.

Thanks,

Sylvain