CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Lilly
Lilly

Reputation: 233

If String Value is empty, 0 , or Null MS Access, SQL, VBA

I'm trying to get figure out a work around for this coding.

Essentially this code checks the values of fields in Table CFRRR if program and language match the values in table attendance Programs and Language.

This normal code works. However, if there is not a matching value for language then the code continues to just loop.

I want to adjust the code to go to the next strSQL if there is no matching language, but I can't get it to run through the next strSQL.

I think my error is that I am not writing the IF/THEN statement correctly. Here's the code:

a = Program
b = language

strSQL = "SELECT TOP 1 userID FROM attendance where attendance.Programs LIKE '*" & a & "*' AND attendance.Language LIKE '*" & b & "*' AND Status = 'Available' AND attendance.Tracking = 0"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If strSQL = Null Then
    strSQL = "SELECT TOP 1 userID FROM attendance where attendance.Programs LIKE '*" & a & "*' AND Status = 'Available' AND attendance.Tracking = 0"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
End IF

Thank you :)

Upvotes: 0

Views: 1707

Answers (1)

John
John

Reputation: 1004

I think you should change it to 

   Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

If rs.recordcount =0  Then

    strSQL = "SELECT TOP 1 userID FROM attendance where attendance.Programs LIKE '*" & a & "*' AND Status = 'Available' AND attendance.Tracking = 0"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
End IF

Upvotes: 1

Related Questions