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

[BillCo]

Using your code:

function intMyResult(startdate as date, enddate as date) as integer

'first get the date difference between the start date and the end date:
intDateCount = datediff(startdate, enddate)

'then loop through the dates and count the number of holiday days
dim dtTemp as date
dim intHolCount as integer

intHolCount = 0
dtTemp = startdate

Do while not dtTemp > enddate
if DiscRate then intHolCount = intHolCount + 1
dtTemp = dateserial(year(dtTemp), month(dtTmp), day(dtTemp) +1)
loop

'now subtract the two for your result
intMyResult = intDateCount - intHolCount

End Function

But this slow and nasty if there's a large date gap - there'll be a
bottle neck at the dlookup in the DiscRate function... especially if
you have a split database.
My advice: Produce a table of working days and do a count query on it
based on accepting the dates as paramaters - there's lots on
paramaterised queries elsewhere.

[BillCo]

> if DiscRate then intHolCount = intHolCount + 1

sorry, that should be:
if DiscRate(dtTemp) then intHolCount = intHolCount + 1

dooh!

[deercreek]

I'm a little slow but, I don't see how this is accoplishing what I'm
after? I need it to send a nuber of days staying that are not a weekend
or a holiday. Based on 2 fields that I have in my form my start and end
dates.

[Mike Preston]

On 12 Dec 2005 09:18:38 -0800, "deercreek" <da*@deercreekcg.com>
wrote:

I'm a little slow but, I don't see how this is accoplishing what I'm
after? I need it to send a nuber of days staying that are not a weekend
or a holiday. Based on 2 fields that I have in my form my start and end
dates.

What you want to do is type (well, cut and paste would be easier) what
Bill gave you right next to the function that you already have (either
below it or above it).

Then, in the textbox that you want to see the "result" displayed in,
change the controlsource to:

=intMyResult(CampStartDate,CampEndDate)

Then type something into CampStartDate and CampEndDate and see what
ends up in your textbox.

But there are 3 corrections to Bill's code that are needed:

1) Add

Dim intDateCount as Integer

as the first line of intMyResult

2) Change Month(dtTmp) to Month(dtTemp) on the line just above the
Loop command

3) Change

intDateCount = DateDiff(startdate, enddate)

to

intDateCount = DateDiff("d", startdate, enddate)

That should do it.

This will give you "#error" in your textbox unless or until both the
dates are filled in. If you want to see something other than "#error"
then you need to add some error checking to Bill's routine.

mike

[BillCo]

Thanks Mike,

The code was meant more as a guidline, but I got a little sloppy there,
must have been too much blood in my coffee stream - sorry deercreed!!!
I really should hit preview before I post
8¬/