Could use so code help!

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?

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 ALTF11. 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" wrote:

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?

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.

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

"deercreek" wrote:

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.

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

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" wrote:

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

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.

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

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" wrote:

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

still needing help if anyone has any ideas