Finding the amount of Fridays & Saturdays in a given date range

T

thelad

New member
Local time
Today, 08:24
Joined
Mar 29, 2009
Messages
6
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
 
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
 
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?
 
Yep, thats it.

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

Tom
 
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.
 
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.
 
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
 
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.
 

Attachments

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
 
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.
 
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.
 
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
Click to expand...
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.
 
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!
 
Last edited:
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
 
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
 
You must log in or register to reply here.

Similar threads

J
Code to change Date in a field if the checkbox is checked
Replies
5
Views
448
LarryE
LarryE
M
Solved Need to count the number of a specific day between two dates in access vba
Replies
9
Views
1,480
BAntar
B
P
HELP-Run-Time Error "3048" - cannot open anymore databases following Microsoft Update.
Replies
2
Views
1,042
Minty
Minty
P
Solved Access Upgrade to 365 (2016) getting compile errors and a notice to update a "Function"
Replies
3
Views
2,299
theDBguy
theDBguy
A
Solved Avoid Duplicated Records by dates - but by a range, not exact date match
Replies
8
Views
779
bob fitz
bob fitz

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom