Finding all records between two dates.. but

# Thread: Finding all records between two dates.. but

1. Senior Member
Join Date
Dec 1969
Posts
205

## Finding all records between two dates.. but

Hi all, <BR><BR>basicly i have a simple problem that i need to solve. I need to select all the records between two dates. It kind&#039;a sounds simple but it really isnt.<BR><BR>I have a query which returns these values <BR>RefNo, BookingDate, Duration<BR><BR>Duration specifies the number of days for that specific booking. This means if a booking is on 02/04/02 with a duration of 3 days, then 03/04/02 is not available.<BR><BR>I am having trouble checking this because all i have is one date with a duration. So if i would like to check whether 05/05/02 is available i would have to check each record&#039;s BookingDate, and also check whether the date falls within the range of the duration of each date. <BR><BR>I am not sure whether to select everything, save it into an array and calculate each date&#039;s duration then check whether the search date would fall within this range. <BR><BR>It would have been easier if there were two dates in the query, coz then i would just use a &#039;between in SQL&#039;. <BR><BR>Any ideas??<BR><BR>Thanks..<BR><BR><BR><BR>

2. Senior Member
Join Date
Dec 1969
Posts
5,104

## RE: Finding all records between two dates.. but

This should give you the 2nd date... <BR><BR>For SQL Server:<BR>DateAdd(dd, Duration, BookingDate)<BR><BR>In Microsoft Access:<BR>DateAdd(d, table.Duration, table.BookingDate)<BR><BR>That should get you started..<BR><BR>-Doug

3. Senior Member
Join Date
Dec 1969
Posts
16,931

## Three options....

Either:<BR>1) Create a field in the SQL query using the date and the duration (add the duration to the date, then use the between function).<BR>2) Create a VIEW in SQL which encompasses this information so that you can easily pull it out.<BR>3) Create a third table which is "BookingDates" or something and contains an entry for EACH of the dates the booking is for.<BR><BR>Obviously, #2 is the most efficient and scalable.<BR><BR>Craig.

4. Senior Member
Join Date
Dec 1969
Posts
205

## RE: Finding all records between two dates.. but

Here is something i did earlier. This only checks whether a date falls within a range of two dates. If it does, it sets a flag, and just exists the loop. <BR><BR>do while not rs.eof and bEvents = false <BR>reservedDate = rs("reserved")<BR>BookedDate = rs("Booked") <BR><BR>if instr(rs("ppe ref"), "C")&#062;0 then<BR> if isNull(rs("Booked Duration")) or NOT rs("Booked Duration")&#062;0 then<BR> BookedDuration = 0<BR> else <BR> BookedDuration = rs("Booked Duration")<BR> BookedEnd = dateAdd("d",BookedDuration,rs("Booked"))<BR> end if<BR> if tempDate &#062;= rs("booked") AND tempDate&#060;BookedEnd then<BR> bEvents = true<BR> cellColor = "#D85959"<BR> end if<BR> <BR> end if<BR> <BR> <BR> <BR> If instr(rs("ppe ref"), "r") &#062;0 then<BR> if isNull(rs("Reserved Duration")) or NOT rs("Reserved Duration")&#062;0 then<BR> ReservedDuration = 0<BR> else <BR> ReservedDuration = rs("Reserved Duration")<BR> ReservedEnd = dateAdd("d",ReservedDuration,rs("Reserved"))<BR> end if<BR> if tempDate &#062;= rs("reserved") AND tempDate&#060;reservedEnd then<BR> bEvents = true<BR> cellColor = "#00CC99"<BR> end if<BR> <BR> end if<BR> rs.movenext<BR> loop<BR><BR><BR>The difference between this and my initial problem is that i would like to display all this dates <BR>but i am having trouble with the sql statement, becuase in order to display them i would have to select one record, calculate its second date, compare the range with my searchDate.. <BR><BR>For example this is the kind of thing i wanna do.. if access spoke simple English<BR><BR>StartDate = bookingDate<BR>EndDate = dateAdd(bookingDate, duration)<BR>select * from bookings where bookingDate = &#039;mySearchDate&#039; OR<BR>mySearchDate also falls within the range between startDate and EndDate<BR><BR><BR>Please Help.. <BR><BR><BR><BR><BR><BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
205

## RE: Three options....

Option one would probably be the easiest for me.. 2 & 3 would probably be advanced stuff for me. <BR><BR>Cheers..

6. Senior Member
Join Date
Dec 1969
Posts
205

## RE: Three Options..

The biggest problem of all is that I dont have write permission to the Query involved so i cannot edit it. But i am able to write a new query to use that query.. :(<BR><BR>Cheers

7. Senior Member
Join Date
Dec 1969
Posts
205

## RE: Three Options..

Im just curious.. <BR>How would i create a new field in the query such that the value of the field is the result of the bookingDate + duration?<BR><BR>select [bookingDate + duration], bookingDate, duration ?????

8. Senior Member
Join Date
Dec 1969
Posts
205

## Is this right??

I just managed to put together this query in Access, it seems to be working. Can you check and tell me if there might be any problems with it.. <BR><BR>And also it is getting its values from another query.. does this mean trouble.. ?<BR><BR>SELECT [all].Reserved, [reserved]+[reserved duration] AS [Reserved End], [all].[reserved duration], [all].Booked, [all].[booked Duration], [booked]+[Booked Duration] AS BookedEnd<BR>FROM [all];

9. Member
Join Date
Dec 1969
Posts
43

## RE: Three Options..

Select bookingdate + duration as bookingduration......

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•