459,624 Members | 1,747 Online
Need help? Post your question and get tips & solutions from a community of 459,624 IT Pros & Developers. It's quick & easy.

Could use so code help!

 P: n/a 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 Dec 12 '05 #1
5 Replies

 P: n/a 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. Dec 12 '05 #2

 P: n/a > if DiscRate then intHolCount = intHolCount + 1 sorry, that should be: if DiscRate(dtTemp) then intHolCount = intHolCount + 1 dooh! Dec 12 '05 #3

 P: n/a 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. Dec 12 '05 #4

 P: n/a On 12 Dec 2005 09:18:38 -0800, "deercreek" wrote: I'm a little slow but, I don't see how this is accoplishing what I'mafter? I need it to send a nuber of days staying that are not a weekendor a holiday. Based on 2 fields that I have in my form my start and enddates. 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 Dec 12 '05 #5

 P: n/a 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¬/ Dec 13 '05 #6