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

Question

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 :)

@Bohemian this is my problem statement: However, if there is not a matching value for language then the code continues to just loop. and.. 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. — Lilly
– Lilly, Commented May 23, 2015 at 21:34
Harsh judgement by Bohemian. Lilly, strSQL will never be null, since your setting it to the SQL string. Check the recordset instead (if it's empty or not). — Mackan
– Mackan, Commented May 23, 2015 at 21:34
Hi @Mackan I agree! I was kind of shocked to be honest, but it's all good. I understand what you're saying. Should I check the recordset after the initial strSQL? — Lilly
– Lilly, Commented May 23, 2015 at 21:37
rs either contains records or not. So you'll need to check if after Set rs = .... But, and this is probably one of the reasons Bohemian put this on hold, I can't see any loop that you describe. I can't follow your logic, but I can say that the if strSQL = null is not correct. — Mackan
– Mackan, Commented May 23, 2015 at 21:42
@Mackan you're right, the loop is at the bottom of this full code. I try not to put full code just because it can be so large and people don't have the time to read the full code. I guess I'll add it for reference. So for the checking after the set rs = ... would something like this: If rs!UserID = Null Then work? Because if the UserID is not set to a value I know there wasn't a language that matched. — Lilly
– Lilly, Commented May 23, 2015 at 21:47

John · Accepted Answer · 2015-05-23 21:58:21Z

1

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

answered May 23, 2015 at 21:58

John

1,0049 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related