By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

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

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. Public Sub Interview_Time_AfterUpdate()
  2. Dim strSQL As String
  3. strSQL = "SELECT Count(*) from tblIntCandInterview WHERE [Interview Date] = '" & Me.Interview_Date & "' and [Interview Time] = '" & Me.Interview_Time & "'" 
  4.  
  5. If strSQL > 0 Then 
  6. MsgBox "Interview Date & Time Conflicts With Another, Please Change Date or Time!" 
  7. Else 
  8. MsgBox "Date & Time of Interview OK"
  9. End Sub
Please help me, what am I doing wrong? Its not erroring when I enter a duplicate date and time??"

Thanks, Charlie
May 6 '10 #1

✓ answered by 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

Share this Question
Share on Google+
3 Replies


Jim Doherty
Expert 100+
P: 897
@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
May 6 '10 #2

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

great answer and thanks again :-)
May 6 '10 #3

Jim Doherty
Expert 100+
P: 897
@CharlieUK
You,re welcome glad you got it sorted :-)
May 6 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.