Checking dates via a select query?


Craig Armitage

Can i just give you some more detail to what im trying to do...

Ive made a flexgrid that shows appointments for staff.. as so...

Date: 11/04/07

Time Staff 1 Staff 2 Staff 3
0900 Customer1 Customer 2
0915 Customer1 Customer 2
0930 Customer3 Customer 2

This all works great and we can see who is supposed to be where...

I would like to now make it edit an appointment when I click on an

I can determine the time and staff member easy enough but to call up the
correct record, I need to find out the ID of the Appointment, the easiest
way is for me to do a select query on the appointments that matches the
Date, the StaffMember and the start/end times.

I can do everything apart from the times. As you can see from the lits,
staff 1's appointment is from 09:00:00 - 09:30:00. When I click in the
flexgrid, the best i can return is the time at the position clicked, so for
example, 09:15:00.

So I need to determine which record has a starttime before that 09:15:00 and
an endtime that is after 09:15:00.

So basically i need to know how to correctly format the following

Dim vRow As Long, vCol As Long, vDay As Long
Dim vDate As Date
Dim vText As String
Dim rst As Recordset
Dim q As String
Dim vTime as String

FlexGridClick flxDates, vRow, vCol, vText
'return Row & Col (or whatever else)
vDate = 0
'set vDate to 0

'if DAY mode
vDate = txtToday
'set todays date

vTime = "09:15:00" 'sample data, real time would come from a click
when ive coded it

If vDate <> 0 Then

q = "SELECT * FROM Appointments WHERE StaffMember = " &
currentstaffmember & " AND AppDate=#" & vDate & "# AND ((AppStartTime <=" &
vTime & ") AND (AppEndTime >=" & vTime & "))"

Set rst = CurrentDb.OpenRecordset(q)

Do Until rst.EOF

MsgBox ("'" & rst!AppStartTime & "'")

Set rst = Nothing
End If

Dale Fye

Are your AppDate, AppStartTime, and AppEndTime text fields, or date/time
From the looks of it, your StaffMember field is numeric, is that the case
(if not you will have to wrap CurrentStaffMember in quotes)?

Assuming that they are date time fields, the following might work for your

strSQL = "SELECT * " _
& "FROM Appointments " _
& "WHERE [StaffMember] = " & CurrentStaffMember _
& " AND #" & CDate(vDate) + CDate(vTime) & "# >= #" & [AppDate]
+ [AppStartTime] & "# " _
& " AND #" & CDate(vDate) + CDate(vTime) & "# <= #" & [AppDate]
+ [AppEndTime] & "#"

Hopefully, this will only return a single record. Otherwise, you have
overbooked your staff.


James Frater


I was wondering if you could help, which bit of code did you use to get
appointments for your staff members into different columns? I've been trying
all different ways but just get it to work?



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question