Could use so code help!

[deercreek]

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

 

[Klatuu]

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]

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

 

[Klatuu]

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

 

[deercreek]

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

 

[Klatuu]

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]

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?

 

[deercreek]

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?

 

[deercreek]

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?

 

[Klatuu]

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]

Sorry I'm not getting this. Are you saying I should have another module
in my form other than the one with the previous code in it. If so How
do i insert one into the form?

 

[Klatuu]

Okay, A Standard module is a module that contains only code. You see a list
of them in your database window when you select Modules.
It is not to be confused with a Form module. A Form Module is created
whenever you put code in any event for any part of the form or any control on
the form.

The CalcworkDays function goes IN A STANDARD MODULE

The GetCampDays sub goes in the FORM MODULE. When you open the form in
design view, enter <ALT>F11. It will open the VBA Editor. In the Project
Pane (should be upper left corner of screen), select the form you are working
on and double click. It will open the Form Module for that form.
Now, at the top of the code pane, you will see two combo boxes. In the
combo box on the left, select General. At the very top of the pane with code
in it will probably be one or more Option statements, like Option Compare
Database.
Just below the last Option statement, paste the GetCampDays sub.

Go back to your form in design view. Right click in the upper left corner
of the form and when you get the drop down, select Properties. Be sure it
says Form in the properties dialog box. Select the Events tab. Select the
Current event. Select Code Builder. Put the line of code to call GetCampDays.
Go back to the form. Select the StartDate control. Right Click to get
properties, select the After Update property. Select Code Builder. Put the
same line of code there. Do the same for the End Date control.

 

[deercreek]

ok in the following code

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

is Me.totalcampdays suppose to be my text box field where the number
is going? I am getting error when i go through form and it brings up
above code dosn't like something.

 

[Klatuu]

That is correct. You will need to change the name to whatever you have your
control named.

 

[deercreek]

Still having one problem. The number will not calculate unles i go to
another record set and then come back.
I know probally needs some type of requery just not for sure what and
where. I tried a few things couldn't get it.

Thanks for help
Dan

 

[Klatuu]

The line Call GetCampDays should be in 3 places.
1. The After Update event of the Start Date Control
2. The After Update event of the End Date Control
3. The Current event of the form

It needs to be in the After Update event of the Start and End Date controls
because you don't know which the user is going to enter first. The way the
code is written, if one of the controls has not been filled in, it will
return Null, which would be the value in the Number of Days for a new record.
When both have a date, it will calculate the days and display it.

It needs to be in the form Current event so each time you move to an
existing record, the number of days will be calculated and displayed.

 

[deercreek]

I have it in those 3 spots and that is what i'm getting as end result
it won't update unless i swith records.

 

[deercreek]

This is the code I ended up with I took away the "+1" from
intTotalDays = DateDiff("d", dtmStart, dtmEnd) becasuse I need the
check in date to count as dicount day if not fri sat sun or holiday,
but not the check out date. I also changed the 5 to 4 because I count
fridays as weekend.
If Weekday(dtmToday, vbMonday) > 4 Then 'It is Saturday or
Sunday

I noticed if i put in dates 12/6/2005 to 12/15/2005 it returns 6 which
is correct, but if i put in
12/15/2005 to 12/23/2005 it returns 4 and it should be 5.


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) > 4 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

 

[Klatuu]

I don't know why it is not working in the After Update events of the
controls. It should. As to the days being returned incorrectly, I will look
into it.
Try tracing the code in debug to see what is happening in the after update
events.

 

[deercreek]

still needing help if anyone has any ideas