Aug 24 2021 11:21 PM
Aug 24 2021 11:21 PM
Say I have table with 2 dates (field1 & field2)
20/12/2020 & 10/01/2021
How would I show the datediff by month in a query
Dec 20 = 12
Jan 21 = 10
Or if the dates were 28/08/2021 & 02/10/2021
Aug 21 = 4
Sep 21 = 30
Oct 21 = 2
Aug 25 2021 07:22 AM
Sometimes it can help to explain the real world problem you need to solve, rather than trying to "simplify" by abstracting out just a few details.
Like Colin, I can't see any logic in the examples provided as raw numbers.
What is the goal? Why are you counting months, or is it days?
Thanks for providing enough context to help us offer suggestions.
Aug 25 2021 12:00 PM
Sorry if I didn’t give enough information – my bad.
I am creating a database for a friend who has a small hotel – I’m not getting paid, but have been promised a good dinner.
She has a number of rooms in her hotel.
The rooms rent out for different amounts – depending on demand.
She would like to know what the bookings were per month.
But this, obviously, has a problem as she may book out a room for (for example) 6 weeks, 2 weeks, 4 days, etc, etc. so on some occasion the bookings would go from one month to another.
So rather than using the start date (e.g. 25/12/2020 for X days) it would be better if she could know what the bookings where per month.
I hope this makes sense.
Using my original post’s details let say some booked a room from 28/08/2021 & 02/10/2021
She would want to know that the room was booked for:
Aug 21 = for 4 days
Sep 21 = for 30 days
Oct 21 = for 2 days
I have tried to create a query to do this but, let just say, I’m not have much luck.
Aug 25 2021 01:52 PM
Thank you. Now that does make sense.
You need to have each booking recorded one of three ways.
Each booking could have a start date and and end date. I.e. a booking for three days starting on December 1st would have an end date of December 3.
Each booking could be recorded on each of the days in the time span, i.e. a booking starting on December 1st would have entries for that booking on 1/12, 2/12 and 3/12
It sounds like your approach is to record the Start date and a second field with the length of stay. That's why you are having a problem with the calculation across month boundaries where you need to know "bookings" per month. A booking could be in two different months, or even three if it is for more than 30 (or 31) days.
I think you also need to pin down what you and your client mean by "bookings". Do you mean the number of times a room is booked for one day or longer? Or, do you mean the number of nights for which a room is booked? Slightly different meanings. Let's say 10 people each "book" the same room for two nights each. That's 10 "bookings" by the first definition, or 20 "bookings" by the second definition. Before we get to a specific suggestion, we do need to pin down that definition.
For me, the second approach mentioned above, one record per room per day it is booked, would probably be the easiest way to manage calculations like this, but it could be done with the other approach, as long as we know what we are counting exactly.
Aug 26 2021 02:51 AM
You can use the query DaysMonthsDateRange found in \demos\Date.accdb at
Id DateStart DateEnd Year Month DateFrom DateTo Days
However, an end date is normally not included in date interval calculations so, to have a count of 2 for September, you would need to add one day to the date of your Field2 and pass that to DateEnd.
DateEnd = DateAdd("d", 1, [Field2])