I have a table that stores information about bookings. The two fields that I am concerned with are ScheduledStart and ScheduledFinish.

I have a function that runs on the booking form before updates that checks the contents of the new ScheduledStart and ScheduledFinish fields (let's call them PropStart and PropFinish for now) against stored records of ScheduledStart and ScheduledFinish for conflicts.

I have decided to use a query (with a count function of the results) where a return of more than zero results will indicate a conflict.

For now, I have the following SQL query that I have made to test the concept, with constants in use where variables will be inserted:

SELECT Booking.*, Booking.Device, Booking.Date, #12:00:00 PM# AS PropStart
From Booking
WHERE (((Booking.Date)=#12/25/2009#) AND ((#12:00:00 PM#) between "ScheduledStart" AND "ScheduledFinish"));

Now, I have some test data for that date, and according to my calculations there should be at least one conflict but the query is not returning anything.

I would be greatful for anyone's insight into this.

Thanks.

WHERE (((Booking.Date)=#12/25/2009#) AND ((#12:00:00 PM#) between "ScheduledStart" AND "ScheduledFinish"));

This is syntactically incorrect. If you booking date includes times as well, which it should not then you need to omit the time elemment
WHERE [Date] = #12/25/2009# AND [Date] Between #" & ScheduledStart # AND " #" & ScheduledFinish & "#;"

Also do not use the word Date a a field name this is an Access reserved word.

David

Thanks for the reply David.

The date field does not store a time value.

The section "((#12:00:00 PM#) between" was generate by MS Access when I created a non-stored field that has a constant value of #12:00#. This costant will be replaced with a VBA variable. I cannot use a field because the data that is being compared to ScheduledStart and ScheduledFinish is not stored.

Thanks.

So you have dates in one field that do not contain a time, and you want to send a from and to date range to the query and see if any records exist within that time frame?

Is this correct?

If so wher does the time element come into it?

How or what were you doing when Access created the previous syntax for you?

What version of Access are you using?

David

David,

I'm using Access 97. Unfortunately, that can't be changed.

Basically, I have a table called booking that stores a data field and then two seperate time fields called ScheduledStart and ScheduledFinish. The date field is dd/mm/yyyy format and the time fields are hh:mm format each.

I want to pass a constant date value and for now one constant time value of the same format into the query to see if it falls between ScheduledStart and ScheduledFinish.

Normally, if the time value in question was a field in the database one might use:

WHERE [InsertFieldNameHere] BETWEEN ScheduledStart and ScheduledFinish

However, because [InsertFieldNameHere] is actually going to be a constant (e.g., "12:00") that will be inserted into the SQL query by means of a VBA variable, I need another approach.

When I used to MS Access query design view facility and created a "fake field" as "PropStart: #12:00#" and then set the criteria as "BETWEEN ScheduledStart and ScheduledFinish", what I quoted on the first post was the result.

However, I have several records where #12:00# should fall between ScheduledStart and ScheduledFinish however nothing is being returned.

Ultimately, this function will be used to check for time conflicts before data is entered into a table. If the query returns more that zero records then there is a conflict.

Thanks for your help and you patience.

The problem with time fields they become hard to manage as times, however if you were to store them as minutes past midnight then they become more manageable.

I think the best approach would be to use a Public Function saved in a standard module.

Public Function OverLap(TimeLower As Date, TimeUpper As Date, CheckTime As Date) As Boolean

If TimeLower > CheckTime Or TimeUpper < CheckTime Then
OverLap = True
Else
Overlap = False
End If

End Function

Then in your query

IsOverlap:OverLap(ScheduledStart,ScheduledEnd,#12: 00:00#)

This is all aricode and untested:

David

I had a similar function worked out before I decided to go down the SQL route. The problem I had was that [Date1] > [Date2] type logic simply did not seem to work with times.

I cannot change the format of the times as I already have over 1500 booking entries!

Syntax wise, the SQL i quoted seems to work, however it does not generate the results that I was expecting.

You may find it helpful if you think about how Date/time values are stored in Access. The Date part is stored as the number of days since 1/1/1900 and the time part is stored as a fraction of a day so 12 Midnight is stored as .0 and 12 Noon is stored as .5 06:00 is stored as .25 and 18:00 is stored as .75

This may explain some of the results you have been getting.

Are you sure that is where your problem is?
I'm on 2002 SP3 and this works fine

SELECT tbltime.id, tbltime.time1, tbltime.time2, #12:00:00 PM# AS atime
FROM tbltime
WHERE (((#12:00:00 PM#) Between [time1] And [time2]));


Even when I used the design grid and put atime: #12:00# , with the result that ACCESS generated 30/12/1899 12:00:00 in both the field and the criteria it still worked fine.

Brian

But even then, 5 would be between 3.5 and say 7.5 so I still don't see why that logic would not work.

How does SQL store date/time data? The same method?

Brian,

Did you try the query with some test data? Did the logic work?

:confused:

Yep it was a test and it pulled the record where time1 was 11:00:00 and time2 was 12:00:00 but no others, as expected

Brian

Brian,

Is there any chance that you could attach the file or send it to me? I'd like to see if it would work with access 97, and then see if there's something that I am doing wrong.

OK

Brian

Just what the doctor ordered!

It turns on I was using quotation marks instread of square brackets.

Thanks everyone for your contributions.

Glad it's fixed, thanks for the feedback, I'm embarressed that I didn't spot that in your first post.

Brian