Relatively Basic Double-booking help!

[margybear]

Hello!
I need a bit of help, I'm making a database based on a beautician's spa.
Basically, I want to use VBA (or queries, if necessary) to check that an appointment isn't already confirmed for a particular appointment.

At the minute, I have a query which contains:
Date of appointment (Format: Date/Time)
Time of appointment (Format: Lookup wizard, so text).

Basically, I have a command button at the minute, which when clicked, should look through the query and perform a count function. This is meant to count the number of fields where the value of both the date AND time are the same as that which is currently entered.
The code is:

If DCount("*", "QRYDuplicates", "Time of Session=#" & Me.Time_of_session & " And Date_of_appointment=#" & Me.Date_of_Appointment & "#") > 0 Then
MsgBox "Sorry, this is taken!", , "Double Booking"
Else
MsgBox "This appointment is available!", , "Appointment Available"
End If

However, I'm hopeless with code. This was taken from a code online, and I don't know how to manipulate it to work for my database. If I click the command button, the error is:

Run-time error '3075':- Syntax error in date in query expression 'Time of session=# And Date of appointment=##'.

If I have left any information out, please tell me!
Many thanks in advance.

 

[Rx_]

Just a quick observation - the Time of Session
does not have underscores, does it need a bracket [Time of Sessions]
SQL is picky about spaces in a table name.

 

[margybear]

I tried putting square brackets in, and then underscores, and then both. No luck unfortunately, it's the same error

 

[jdraw]

Try

If DCount("*", "QRYDuplicates", "[Time of Session]='" &
Me.Time_of_session &
"' And Date_of_appointment=#" & Me.Date_of_Appointment & "#") > 0 Then
MsgBox "Sorry, this is taken!", , "Double Booking"
Else
MsgBox "This appointment is available!", , "Appointment Available"
End If

This is assuming Time of Session is text.

 

[margybear]

Try

If DCount("*", "QRYDuplicates", "[Time of Session]='" &
Me.Time_of_session &
"' And Date_of_appointment=#" & Me.Date_of_Appointment & "#") > 0 Then
MsgBox "Sorry, this is taken!", , "Double Booking"
Else
MsgBox "This appointment is available!", , "Appointment Available"
End If

This is assuming Time of Session is text.

Okay, I tried this. But now it sems to accept every single booking, even when I know there are duplicate values. So the coding isn't working, but thank you.
Is there any way I other way I could do it? Could I run a query to check for duplicates, or is that just too complicated?

By the way, the "time of session" field is a lookup table, but the values are text.

 

[margybear]

If there's no practical solution, please tell me, so I don't have to worry about fixing it!

 

[margybear]

Hello! I found the solution. The error messages were the wrong way round.
The final code is:

Private Sub Command138_Click()
If DCount("*", "QRYDuplicates", "[Time_of_Session]='" & Me.Time_of_Session & "' And [Date_of_appointment]=#" & Me.Date_of_Appointment & "#") > 0 Then
MsgBox "This appointment is available!", , "Appointment Available"
Else
MsgBox "Sorry, this is taken!", , "Double Booking"
End If
Exit_Command138_Click:
    Exit Sub
Err_Command138_Click:
    MsgBox Err.Description
    Resume Exit_Command138_Click
 
End Sub