Sql Problem

Hi

I have created an appointment booker GUI in VB that uses a msaccess db as a backend.

I have an appointment table that consists of multiple fields, the main ones are a [STARTDAY],[ENDDAY],[STARTTIME],[ENDTIME] my problem is what i would like to do is if an employee enters and appointment but already has that time duration filled by another appointment I would like to notify the user telling them they cannot use this time frame because they have an existing appointment already, I have done the coding and thought that I had figured out the SQL Statement for this however sometimes it works and sometimes it does not. Please Help, or if anyone has any other ideas of how i can do this your help would be greatly appreciated.

The tempremental query that I have written is
SELECT EMPID,STARTDAY,STARTTIME,ENDTIME" & _
" From TAppointment" & _
" WHERE STARTTIME=(SELECT Max(MaxAppt.STARTTIME) AS MaxStartTime" & _
" FROM TAppointment As MaxAppt WHERE StartTime <= #" & endtime & "#) AND EMPID = '" & empid & "' AND STARTDAY = #" & startday & "# "

Wouldn't you want to pass in the actual starting TIME of the appointment they want to make, and then check to make sure that start TIME isn't between any start and end times for that user for that day?

It may be something like:

"SELECT EMPID, STARTDAY, STARTTIME, ENDTIME " & _
"FROM TAppointment " & _
"WHERE #" & newApptStartTime & "# BETWEEN " & _
"STARTTIME AND ENDTIME " & _
"AND EMPID = '" & empid & "' AND " & _
"STARTDAY = #" & startday & "# "

Then if it returns no results, they can add the time.

Again, I'm not sure if Access supports the BETWEEN syntax.

Hope that helps,

Jill

Suggestion: You could use a Function/Trigger/Stored Proc for this one.