Using SQL statement in VBA to do a count then use IF statement on result!!

[CharlieUK]

Hi Guys,
Im trying to get this VBA code to work so if the SQL returns a count of 1 or more then the msgbox will appear and the user cannot add the date and time. This is to stop conflicts of booking 2 interviews on the same date and time:

Public Sub Interview_Time_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT Count(*) from tblIntCandInterview WHERE [Interview Date] = '" & Me.Interview_Date & "' and [Interview Time] = '" & Me.Interview_Time & "'" 
 
If strSQL > 0 Then 
MsgBox "Interview Date & Time Conflicts With Another, Please Change Date or Time!" 
Else 
MsgBox "Date & Time of Interview OK"
End Sub

Please help me, what am I doing wrong? Its not erroring when I enter a duplicate date and time??"

Thanks, Charlie

[Jim Doherty]

@CharlieUK
Hi Charlie

Look at your code again...

1) You are only defining the variable strSQL as a string and then setting a string value to that variable and not opening any recordset based on that SQL in order to do any counting test.

2) The strSQL>0 makes no sense in this context (ie if you believe you are testing a recordset at this point then you are mistaken)
.
3) The use of concatenation when referring to dates should use # not the apostrophe when wrapping a date field

4) When creating table fields name them without spaces.

Have a look at the DCount function in Access it is documented to return a count of the records that satisfy any of your criteria

If you wish to proceed in the context of opening a recordset and counting on that, you need to look at the OpenRecordSet method. There are plenty of references to it on site........ seek and you shall find :)

If you get really stuck then come back to me. I would rather you fully understood each piece in sequence rather than just the solution straight away

[CharlieUK]

@Jim Doherty
thanks Jim, ive sorted it with DCount method using a query!

great answer and thanks again :-)

[Jim Doherty]

@CharlieUK
You,re welcome glad you got it sorted :-)