Problems creating query to list dates between "Start Date" and "End date"

[Windward]

Hi



I am new to access and on a steep learning curve. I don't use VBA or SQL (Yet).

I am trying to create a booking management system for our 3 room small B & B. I have created and populated all the tables and relationships and everything talks to each other. However I have made an oversight and missed a trick.

Bookings each have a guest_ name, start_date, end_date and_room allocated etc.

Rooms can be closed out for a night. The close outs use the same start_date and room_allocated. All of these dates are kept in seperate linked tables to prevent duplication.

I need to be able to count the number of individual room nights between a variable number of days in any given period. I have used a parameter query that can count the number of room nights closed out, but I cannot count the number of room nights as any query I make only looks to a start date or end date falling in the given period, not any days in between.

For example, if I have a guest staying between 1-April-12 to 13-April-12 and I am searching for occupied rooms between 3/4/12 and 10/4/12 it wouldn't pick this guest (or the occupied rooms) up. If I was searching between 30th March and 3rd April it would.

I have assumed that I could create a query to build a table of dates for each booking, but I am not sure if this would be wasteful and would rather get the result each time I query, rather than create a new table. Anyhow, I can't seem to creat the table anyway, and to go outside to build it in excel and import seems a bit defeatist.

My head hurts, can anyone suggest an Access solution (not for the headache!)

[recyan]

@Windward,
Haven't read your problem in detail. However, whenever someone posts a problem with date range, this is the first thing that comes to my mind.

http://www.baldyweb.com/OverLap.htm

Check out if above is of any help.

Thanks

[Windward]

Many thanks recyan

Definitely a "wood for the trees" moment. I just couldn't see the answer for looking!

I have created a query with columns for Name, StartDate, EndDate and "Room Nights"

StartDate has the criteria <=[Forms]![Occupancy Dates]![PeriodEnd]
EndDate has the criteria >=[Forms]![Occupancy Dates]![PeriodStart]

Used a form to get values "PeriodStart" and "PeriodEnd" and set up a calculated field for "Room Nights"

Room Nights: (IIf([EndDate]>[Forms]![Occupancy Dates]![PeriodEnd],[Forms]![Occupancy Dates]![PeriodEnd],[EndDate])) - (IIf( [StartDate]< Forms![Occupancy Dates]![PeriodStart], Forms![Occupancy Dates]![PeriodStart] , [StartDate] ))

It works a treat!

[recyan]

@Windward,
We will both together along with many others, thank pbaldy for the little beauty, that he has posted.
Glad you got things working.

Thanks