Could use so code help!

[Ron2005]

Relative to the "answer" being 4 instead of 5:
The reason is very simple, 12/23 is a Friday and you have said NOT to
count Fridays as one of the days.
Now the "resolution" to that problem is probably no where near as
simple. To even attempt at a programmatic solution I would need to see
a whole series of date ranges incrementing from 2 days through the max
that you would want and have them start on the same date and tell us
the answers that you think you want the logic to return. Given that we
could probably figure out the logic of when to count friday and when
NOT to count friday (or for that matter Saturday or Sunday).

Ron

 

[Ron2005]

It does not have to really be the MAX that you would expect but just
cover at least 2 weeks so the rule for Friday, Saturday and Sunday at
the end can be figured out.

Ron

 

[deercreek]

I'm not sure I am following you. As far as the logic behind it. I need
it to count the checkin date , but not count check out date. and if any
date falls on fri sat sun or holiday then it should not count for
instance check in 12/7/05 check out 12/12/05 it should return 2 for the
7th and 8th. Or check in 12/5/05 check out 12/23/05 should return 12
for the 5th-8th 12th-15th and 19th -22nd. Hope this helps

 

[Ron2005]

OK, I misunderstood the earlier post.
Then I suggest the following change. Don't subtract the days NOT to
count but rather add up the number of days to count.

Here is the replacement logic:


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

dtmEnd = DateAdd("d", -1, dtmEnd) 'don't count last day
therefore don't even look at it

intTotalDays = 0 'Startwith 0 days ' We have 0 days to
start
dtmToday = dtmStart
'InitiateCompare Date

Do Until dtmToday > dtmEnd
intTotalDays = intTotalDays + 1 ' Assum we will count
then take our weekend and Holiday
If Weekday(dtmToday, vbMonday) > 4 Then 'It is
Saturdayor Sunday
intTotalDays = intTotalDays - 1 'Take one
dayaway for weekend
ElseIf Not IsNull(DLookup("[Holidate]", "Holidays", _
"[Holidate] = #" & dtmToday & "#")) Then 'It is
aHoliday
intTotalDays = intTotalDays - 1 'Take one
dayaway for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day
forNext Compare
Loop
'Until dtmToday > dtmEnd

'All days havebeen compared

CalcWorkDays = intTotalDays 'Return the
Value
===========================================

Try that in place

 

[deercreek]

Ok put code in module and tried it out in immediate window, and I got
the following error.

Cmpile Error: Ambigous name detected :calcworkdays

is that saying it dosn't find that name?

 

[Douglas J. Steele]

No, it's saying that you have two objects in your application named
calcworkdays. Use Find to locate them, and either delete or rename one of
them.

 

[deercreek]

That fixed it thanks. Still having the problem where th text field wont
update unless I change record sets and come back. Anyone have any ideas?