Could use so code help!

I could use a little help from a good code writer out there. I found
some code and modified it a bit for my needs but, I need a little help
to finish it up. What I am trying to due is to get a number to fill out
a text box on my form. I want it to look at the form and get the
CampStartDate and my CampEndDate also to look at a table of holidays.
Then I want the text box to be filled with the number of days they will
be staying that do not fall on a fri sat or sun or holiday. The code I
have below is able to look at single date and determine if it is a fri,
sat, sun or holiday. When it is it returns a -1 value. I would like
to incorporate this code to do what I asked above. It's just a little
out of my league. Anyone that could help I would appreciate it.

Code

Option Compare Database

Function DiscRate(TheDate) As Integer

DiscRate = False
TheDate = Format(TheDate, "dd/mm/yyyy")
' Test for Friday, Saturday or Sunday.
If WeekDay(TheDate) = 6 Or WeekDay(TheDate) = 7 Or WeekDay(TheDate)
= 1 Then
DiscRate = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
DiscRate = True
End If

End Function

Thanks
Dan

Here is a function that will do what you want. It determines the number of
days between two dates and excludes, Saturdays, Sundays, and dates in the
Holiday Table:

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
'D Hargis
'Calculates the number of working days between two dates
'dtmStart - the first day to include in the range
'dtmEnd - the last day to include in the range
'Returns the number of working days between the two dates
'Both dates are counted if they are working days

Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday dtmEnd
If Weekday(dtmToday, vbMonday) 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holidate]", "Holidays", _
"[Holidate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function


"deercreek" wrote:

I could use a little help from a good code writer out there. I found
some code and modified it a bit for my needs but, I need a little help
to finish it up. What I am trying to due is to get a number to fill out
a text box on my form. I want it to look at the form and get the
CampStartDate and my CampEndDate also to look at a table of holidays.
Then I want the text box to be filled with the number of days they will
be staying that do not fall on a fri sat or sun or holiday. The code I
have below is able to look at single date and determine if it is a fri,
sat, sun or holiday. When it is it returns a -1 value. I would like
to incorporate this code to do what I asked above. It's just a little
out of my league. Anyone that could help I would appreciate it.

Code

Option Compare Database

Function DiscRate(TheDate) As Integer

DiscRate = False
TheDate = Format(TheDate, "dd/mm/yyyy")
' Test for Friday, Saturday or Sunday.
If WeekDay(TheDate) = 6 Or WeekDay(TheDate) = 7 Or WeekDay(TheDate)
= 1 Then
DiscRate = True
' Test for Holiday.
ElseIf Not IsNull(DLookup("HoliDate", "Holidays", "[HoliDate]=#" _
& TheDate & "#")) Then
DiscRate = True
End If

End Function

Thanks
Dan

Couple of questions,
1. Dose this go into an event procedure for something like got focus.
2. Do i need to change out the dtmStart to my forms date field info. if
so I have to put in the ([Forms]![Taking
Reservations]![CampStartDate]) for instance.
3. How can I test this with imediate field in a module to make sure
it's doing what I want it to do.

Thanks Again
Dan

"deercreek" wrote:

Couple of questions,
1. Dose this go into an event procedure for something like got focus.
No, I would put it in a standard module. I have one named modDateFunctions
2. Do i need to change out the dtmStart to my forms date field info. if
so I have to put in the ([Forms]![Taking
Reservations]![CampStartDate]) for instance.
To get a result, you will need to have both dates filled in. I would
suggest you put a sub in the General section of your form module that will
check to be sure both dates are filled in and return Null if they are not and
the calculation if they are. Then call the sub in the After Update event of
both date controls (see example below)
3. How can I test this with imediate field in a module to make sure
it's doing what I want it to do.
After you you have placed it in a standard module. In the immediate window
type:
?CalcWorkDays(#12/1/2005#, #12/31/2005#)
or any two dates you want to test with

Thanks Again
Dan


Sub GetCampDays()
If IsNull(Me.CampStartDate) or IsNull(Me.CampEndDate) Then
Me.TotalCampDays = Null
Else
Me.TotalCampDays = CalcWorkDays(Me.CampStartDate, Me.CampEndDate)
End If
End Sub

I still don't get it I got code to work way i want few tweeks of your
code but I'm still don;t get how to put the module into form so it will
preform function. Can anyone help.

Thanks
Dan

The CalcWorkDays function should not go in your form. It should go in a
standard module. In your database window, select Modules. If you have any
modules there, you could paste it into one; otherwise, create a new module
and paste it into the new module. DO NOT name the module the same name as
the function. That causes an error.

The other code I posted goes in the General section of the form module. At
the top of the form module, select General, and put it below any Option
Statements or Module level Dim statements.

"deercreek" wrote:

I still don't get it I got code to work way i want few tweeks of your
code but I'm still don;t get how to put the module into form so it will
preform function. Can anyone help.

Thanks
Dan

I have it in module like yo said my confussion i gusses is how when i
reach the selected text field dose the module when to run and ho is it
getting the dates from my date fields?

Let me try this again. I have it in module like you said to do. My
confussion is, how dose the module know whento run when I have reach
the selected text field. Also how is the code getting the dates from my
date fields in the form?

Let me try this again. I have it in module like you said to do. My
confussion is, how dose the module know whento run when I have reach
the selected text field. Also how is the code getting the dates from my
date fields in the form?

If you put this in your form module as I suggested:
Sub GetCampDays()
If IsNull(Me.CampStartDate) or IsNull(Me.CampEndDate) Then
Me.TotalCampDays = Null
Else
Me.TotalCampDays = CalcWorkDays(Me.CampStartDate, Me.CampEndDate)
End If
End Sub

Then in the After update of Both the CampStartDate and CampEndDate text
boxes, you would put this code:

Call GetCampDays

It will use the values in your cotrols and load the results in the control
where you want to display the number of days. Of course, I don't know the
exact name of your controls, so mine are made up. You will have to
substitute your own.

Also, if you want it to calculate for an existing record, then put the
CallCampDays line of code in the Current event of your form. Hopefully, you
are not storing the number of days in your table. That is a database design
nono.

"deercreek" wrote:

I have it in module like yo said my confussion i gusses is how when i
reach the selected text field dose the module when to run and ho is it
getting the dates from my date fields?