Passing Fields to Module for Calculation in Query

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

I have a module setup that calculates the number of
working days between two dates. I got the code in the
knowledge base. I know how to call the function in my
query and it works if my fields are named BegDate and
EndDate. Work_Days([BegDate], [EndDate]) But in the table
the fields I want to use are Date Initiated and Date
Observed. How do I pass these dates over? What in the
code would I need to change to accept these two dates?
Here is the code:

Function Work_Days(BegDate As Variant, EndDate As Variant)
As Integer

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And Format
(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

End Function

Any info would be great. Thanks
 
You don't need to change the code. In your query, just use your field names
instead, as in:

Work_Days([Date Initialed], [Date Observed])
 
Thanks it works great. I thought I tried that already but
I must have put something in wrong. Probably left the
space out in the field names.

-Patrick

-----Original Message-----
You don't need to change the code. In your query, just use your field names
instead, as in:

Work_Days([Date Initialed], [Date Observed])


I have a module setup that calculates the number of
working days between two dates. I got the code in the
knowledge base. I know how to call the function in my
query and it works if my fields are named BegDate and
EndDate. Work_Days([BegDate], [EndDate]) But in the table
the fields I want to use are Date Initiated and Date
Observed. How do I pass these dates over? What in the
code would I need to change to accept these two dates?
Here is the code:

Function Work_Days(BegDate As Variant, EndDate As Variant)
As Integer

Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And Format
(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays

End Function

Any info would be great. Thanks
Click to expand...


.
Click to expand...
 
Back
Top