Number of days

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
------------------------------------------------------------------------------------------------
 
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)
 
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.
 
Put it into a new field in the query:

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

I think that should work
 
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'
 
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])
 
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


Tommy2326 said:
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 said:
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'
Click to expand...
Click to expand...
 
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

scubadiver said:
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


Tommy2326 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Tommy2326 said:
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

scubadiver said:
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


Tommy2326 said:
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
Click to expand...
Click to expand...
Click to expand...
 
The only problem left is that the available time is not being picked up from
the table.
Click to expand...

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]
 
Back
Top