Finding the amount of Fridays & Saturdays in a given date range (1 Viewer)

thelad · Mar 29, 2009

Hi All,

Hope this is in the right place as I'm a new poster

I'm in the process of making a database for use with hotel reservations and billing.. I've made a form to put new reservations into the table. Here are the relevant fields:

Check-in date
Check-out date
Weekday nights
Weekend (Fri and Sat) nights

The weekend rate for the hotel in question is slightly higher you see. So is there some kind of expression that I can put in the two 'night' fields to calculate the relevant amounts of either Sun-Thurs nights and Fri-Sat nights?

Tom

wiremonkey22 · Mar 29, 2009

I would say take a look at the Weekday function something like

select case Weekday(yourField)

case 1 'sunday'

case 2 'monday'

etc.........

http://www.csidata.com/custserv/onlinehelp/VBSdocs/vbs220.htm

Just an idea.

RuralGuy · Mar 29, 2009

This link might help: http://www.mvps.org/access/datetime/date0011.htm

thelad · Mar 29, 2009

I'm not that handy with VB as others.. Where in that MVPS link would I insert my own fields (check in date and check out date) to make the code work?

Tom

RuralGuy · Mar 29, 2009

So you need to know how many days during the stay are weekdays and how many are Fridays and Saturdays right? Or did I not understand your requirement?

thelad · Mar 29, 2009

Yep, thats it.

There is a weekday rate for Sun, Mon, Tues, Wed, Thurs nights and a weekend rate for Fri, Sat nights.

Tom

RuralGuy · Mar 29, 2009

So you will need two Public Functions right? HowManyWEdays() and HowManyWeekDays() Can you take a stab at either of these functions based on the link I supplied. The Functions will go in a Standard Module named basFunctions.

RuralGuy · Mar 29, 2009

Now that I think about it, just one Function is needed since you can determine how many total days yourself and subtract right? Maybe just the WEdays function.

thelad · Mar 29, 2009

I gave it a bash and copied and pasted the codes from the link into the code section of the form but did nothing.. Shouldn't I be putting something in in relation to the fields that I have?

Tom

Mike375 · Mar 29, 2009

Here is another way. Go t0 post 10 for Raskew's solution

http://www.access-programmers.co.uk/forums/showthread.php?t=168783

The attached DB has Raskew's code in a module and a query using his function.

Note if the day to be counted is the starting date and/or the finishing date then the two days are counted. I put a switch() in the query to exclude those days.

thelad · Mar 29, 2009

Hmm.. Unfortunately I seem a little out of my depth. Is there a way to do this throug the Expression Builder or Control Source?

Tom

Mike375 · Mar 29, 2009

The attached DB is ready to go. The code is in the module and the function name is CountDays which has been used in the query. The number used such as the 6 is for the day of the week with Sunday being a 1

You simply replace the field names S and F with your field names that contain your starting and finishing dates.

Mike375 · Mar 29, 2009

PS.

If you used the attached DB the combinations in the field using Switch need to be checked. I think they are OK for only counting the days between the dates but I might have messed up as I only did it to play around.

RuralGuy · Mar 29, 2009

thelad said:
I gave it a bash and copied and pasted the codes from the link into the code section of the form but did nothing.. Shouldn't I be putting something in in relation to the fields that I have?

Tom

As I said in Post #7, the code should go in a Standard Module named basFunction; not in the code (class) module of your form.

raskew · Mar 30, 2009

Hi -

Here's another one that will allow user to specify specific weekdays to be
billed at the weekend rate. Copy to a standard module and call as shown
in the example.

Code:

Public Function BillingSched(s As Date, _
                             e As Date, _
                             weday As String) As String
'Purpose:  Display total billing days, weekday billing
'          days and weekend billing days based on start(s)
'          day and end(e) day, and days to be billed as
'          weekend days, as specified by user.
'Re:       http://www.access-programmers.co.uk/forums/showthread.php?p=825849#post825849
'          http://www.access-programmers.co.uk/...d.php?t=168783
'Coded by: raskew
'Input:    ? BillingSched(#3/28/2009#, #4/15/2009#, "67") NOTE: Friday(6) & Saturday(7)
'                                                         to be billed as weekend days
'Output:   "18 days billed: 13 week days + 5 weekend days"

Dim n       As Integer
Dim x       As Integer
Dim wdHold  As Integer
Dim intTot  As Integer
Dim strDays As String
Dim strHold As String

   strDays = "1234567123456"
   intTot = DateDiff("d", s, e) 'total days billed
   wdHold = (intTot \ 7) * (7 - Len(weday)) 'number of weekday billing days in full weeks
   strHold = Mid(strDays, InStr(strDays, WeekDay(s)), intTot Mod 7) 'weekdays beyond full weeks
   x = Len(strHold)
   For n = 1 To Len(weday) 'how many week billing days beyond full weeks?
     x = x + (InStr(strHold, Mid(weday, n, 1)) = 0) 'boolean stmt: True = -1, False = 0
   Next n
   wdHold = wdHold + x 'total weekday billing days

   BillingSched = intTot & " days billed: " & wdHold & " week days + " & (intTot - wdHold) & " weekend days"

End Function

HTH - Bob

Added:

Correction required. See Post #17. Sorry 'bout that!

RuralGuy · Mar 30, 2009

Bob,
You just have too much fun with this stuff.

raskew · Mar 30, 2009

Allan -

You're probably right. This was a good'un. But now, after running some more exercises with this case, discover that this needs to be changed:

x = x + (InStr(strHold, Mid(weday, n, 1)) = 0) 'boolean stmt: True = -1, False = 0

... to

x = x + (InStr(strHold, Mid(weday, n, 1)) > 0) 'boolean stmt: True = -1, False = 0

I need to learn to run more tests with something like this, particularly when using boolean statements.

Best wishes - Bob

Mike375 · Mar 31, 2009

RuralGuy said:
Bob,
You just have too much fun with this stuff.

Allan,

Whenever I need something on dates/time and I am stuck I simply go to Bob's profile and his previous posts

RuralGuy · Mar 31, 2009

Mike375 said:
Allan,

Whenever I need something on dates/time and I am stuck I simply go to Bob's profile and his previous posts

Hi Mike...me too.

thelad · Mar 31, 2009

Just a post to say thanks for all your help - I've been fiddling around with the values to put them into the database and should get to a conclusion soon.

Tom

Finding the amount of Fridays & Saturdays in a given date range (1 Viewer)

New member

Registered User.

AWF VIP

New member

AWF VIP

New member

AWF VIP

AWF VIP

New member

Registered User.

Attachments

New member

Registered User.

Registered User.

AWF VIP

AWF VIP

AWF VIP

AWF VIP

Registered User.

AWF VIP

New member

Similar threads

Users who are viewing this thread