Querying Time Range values in Access

[Jhongski]

I want to know which is the best way to create a SQL statement in MS Access to query the Time range value.

Table : LOGTIMES

Sample: I got two fields that is used for storing the schedule (SCHEDFROM and SCHEDTO) and both are of the DATETIME data type

Suppose this is the schedule of an employee
SchedFrom SchedTo
7:00 8:30
8:45 9:30

Scenario: I want to check if the new record containing SchedFrom: 9:00 and SchedTo 10:00 will conflict with the available scheds above. Technically, this should have a conflict since anytime between 9:00 to 9:30 is allocated to the 2nd sched above. Unfortunately, my query below doesn't detect this properly. Don't know if I still need any string conversions.

Query:
SELECT * FROM LOGTIMES WHERE SCHEDFROM >=#9:00# AND SCHEDTO <=#10:00#

 

[lupins46]

SELECT * FROM LOGTIMES WHERE (SCHEDFROM >=#9:00# AND SCHEDFrom <=#10:00#) or (SCHEDto >=#9:00# AND SCHEDto<=#10:00#)

 

[PHV]

If in a form, in the BeforeUpdate event procedure of the form:
If DCount("*", "LOGTIMES", "(#" & Me![SchedFrom control] & "# Between SchedFrom And SchedTo) OR (#" & Me![SchedTo control] & "# Between SchedFrom And SchedTo)" > 0 Then
MsgBox "Conflict"
Cancel = True
Me![SchedFrom control].SetFocus
Exit Sub
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhongski]

I'm using VB-ADODB, how will I able to use the DCOUNT? Is this similar to the record count in ADODB?

 

[PHV]

Use a recordset with SQL code like this:
strSQL = "SELECT * FROM LOGTIMES WHERE #" & Me![SchedFrom control] & "# Between SchedFrom And SchedTo) OR (#" & Me![SchedTo control] & "# Between SchedFrom And SchedTo)"
And test the BOF and EOF properties of the opened recordset.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jhongski]

Hi guys, thanks for replying to my thread. Anyway, I got another problem in my query. Im created a VB function that will return the value of TRUE if a particular time range does not conflict with the existing data. Assuming this is the data:

SchedFrom SchedTo
7:00 8:30
9:00 10:00

10-11am should be valid time since there are no schedules yet for that time frame, but unfortunately using the query below it becomes invalid since 10:00 is the SchedTo of record # 2 already.

SELECT * FROM LOGTIMES WHERE (SCHEDFROM >=#10:00# AND SCHEDFrom <=#11:00#) or (SCHEDTo >=#10:00# AND SCHEDTo<=#11:00#)

Will using BETWEEN statement solve this problem?

 

[PHV]

Use > and > instead of >= and <=

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244