Number of days

[Tommy2326]

I've got the following function in my database, taken from another thread on
here. It is used to calculate the number of days in a date range, the date
range is taken from a form 'Select Date Form' with fields 'txtStartDate' and
'txtEndDate'. I then need to take this number of days and multiply it by the
daily available time, giving me the available time over that number of days.
the daily available time is always the same. How do I use the answer from
the function to calculate this?

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays tabl
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Functio
------------------------------------------------------------------------------------------------

 

[Klatuu]

Just multipy the results by the available time per day. It returns and
integer. If you need to do it in one operations:

lngTotalTime = lngDailyAvailable * calcworkdays(Me.txtStartDate,
Me.txtEndDAte)

 

[Tommy2326]

I dont know how to link the answer from the function to the query to carry ou
the calculation. Where would that bit of code go? Would you have to define
'lngTotalTime' and 'lngDailyAvailable' somewhere in the function? I need to
display the answer somewhere so I can use it in a report.

 

[scubadiver]

Put it into a new field in the query:

lngTotalTime: lngDailyAvailable * calcworkdays(Me.txtStartDate,
Me.txtEndDAte)

I think that should work

 

[scubadiver]

You say: I then need to take this number of days and multiply it by the
daily available time.

I presume this "available time" is a field somewhere and Klatuu has called
it 'lngDailyAvailable'

 

[Tommy2326]

I'm getting an error message: Undefined function 'CalcWorkDays' in expression

The expression I used is:

Time Available:
[Tables]![tblAvailableTime]![Daily]*CalcWorkDays([Me].[txtStartDate],[Me].[txtEndDate])

 

[scubadiver]

I assume you have put the function into a module. If you have called the
module "calcworkdays" change it to something else like "NoDays".

That maybe the reason for the error.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I'm getting an error message: Undefined function 'CalcWorkDays' in expression

The expression I used is:

Time Available:
[Tables]![tblAvailableTime]![Daily]*CalcWorkDays([Me].[txtStartDate],[Me].[txtEndDate])

You say: I then need to take this number of days and multiply it by the
daily available time.

I presume this "available time" is a field somewhere and Klatuu has called
it 'lngDailyAvailable'

 

[Tommy2326]

The only problem left is that the available time is not being picked up from
the table. The available time is stored in the table 'tblAvailableTime' in
field 'Daily'. Just now I'm having to type the available time.

Thanks for the help

Tommy

I assume you have put the function into a module. If you have called the
module "calcworkdays" change it to something else like "NoDays".

That maybe the reason for the error.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I'm getting an error message: Undefined function 'CalcWorkDays' in expression

The expression I used is:

Time Available:
[Tables]![tblAvailableTime]![Daily]*CalcWorkDays([Me].[txtStartDate],[Me].[txtEndDate])

You say: I then need to take this number of days and multiply it by the
daily available time.

I presume this "available time" is a field somewhere and Klatuu has called
it 'lngDailyAvailable'

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

I dont know how to link the answer from the function to the query to carry ou
the calculation. Where would that bit of code go? Would you have to define
'lngTotalTime' and 'lngDailyAvailable' somewhere in the function? I need to
display the answer somewhere so I can use it in a report.

:

Just multipy the results by the available time per day. It returns and
integer. If you need to do it in one operations:

lngTotalTime = lngDailyAvailable * calcworkdays(Me.txtStartDate,
Me.txtEndDAte)


--
Dave Hargis, Microsoft Access MVP


:

I've got the following function in my database, taken from another thread on
here. It is used to calculate the number of days in a date range, the date
range is taken from a form 'Select Date Form' with fields 'txtStartDate' and
'txtEndDate'. I then need to take this number of days and multiply it by the
daily available time, giving me the available time over that number of days.
the daily available time is always the same. How do I use the answer from
the function to calculate this?

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[Tommy2326]

I've got that working now. I put the 'Daily' field in the query, made it
invisible. then used the expression:

Time Available: Sum([Daily]*CalcWorkDays(Forms![Select Date
Form]!txtStartDate,Forms![Select Date Form]!txtEndDate))

Thanks for the help

The only problem left is that the available time is not being picked up from
the table. The available time is stored in the table 'tblAvailableTime' in
field 'Daily'. Just now I'm having to type the available time.

Thanks for the help

Tommy

I assume you have put the function into a module. If you have called the
module "calcworkdays" change it to something else like "NoDays".

That maybe the reason for the error.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

I'm getting an error message: Undefined function 'CalcWorkDays' in expression

The expression I used is:

Time Available:
[Tables]![tblAvailableTime]![Daily]*CalcWorkDays([Me].[txtStartDate],[Me].[txtEndDate])



:


You say: I then need to take this number of days and multiply it by the
daily available time.

I presume this "available time" is a field somewhere and Klatuu has called
it 'lngDailyAvailable'

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video


:

I dont know how to link the answer from the function to the query to carry ou
the calculation. Where would that bit of code go? Would you have to define
'lngTotalTime' and 'lngDailyAvailable' somewhere in the function? I need to
display the answer somewhere so I can use it in a report.

:

Just multipy the results by the available time per day. It returns and
integer. If you need to do it in one operations:

lngTotalTime = lngDailyAvailable * calcworkdays(Me.txtStartDate,
Me.txtEndDAte)


--
Dave Hargis, Microsoft Access MVP


:

I've got the following function in my database, taken from another thread on
here. It is used to calculate the number of days in a date range, the date
range is taken from a form 'Select Date Form' with fields 'txtStartDate' and
'txtEndDate'. I then need to take this number of days and multiply it by the
daily available time, giving me the available time over that number of days.
the daily available time is always the same. How do I use the answer from
the function to calculate this?

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

 

[John W. Vinson]

The only problem left is that the available time is not being picked up from
the table.

Are you still using the invalid syntax [Tables]![tblAvailableTime]![Daily]?
There is no [Tables] collection that you can use in this way. If
tblAvailableTime is not included in the form's Recordsource, use

DLookUp("[Daily]", "[tblAvailableTime]", <some suitable criteria to pick the
right record>)

If it is part of the form's recordsource query use Me![Daily].

John W. Vinson [MVP]