[SOLVED] VBA "does not equal" code not stepping through IF statement as expected

VBA "does not equal" code not stepping through IF statement as expected

There are 2 separate data sources with a Status Code listed. One status code is an Autonumber and the other is a string (Access table and SQL table, respectively).

I'm looking to see if the Status feilds from each record set match and if they don't, make updates.

If rst!StatusCodeID <> CLng(DLookup("[StatusCodeID]", "ref_StatusCode", "[StatusCode] = '" & rstLocal!STATUS_CODE & "'")) Then
      rst.Edit
End if

Each time the break hits the If statement it skips the edit even when the two record sets don't match. I set a debug.print to the immediate window and here are the results (ID, rst!StatusCodeID, rstLocatl!STATUS_CODE:

243011, 3, 3   
429564, 1, 0 
478870, 1, 0  
502598, 1, 0

ref_StatusCode Table:

| StatusCodeID | STATUS_CODE |    Desc   |
|      1       |      0      |  Inactive |
|      2       |      1      | Withdrawn |
|      3       |      3      |   Active  |

Solution

It appears that the field name in the table is STATUS_CODE instead of StatusCode, and since the field is numeric, removing the single quotes is necessary. Using single quotes for numeric fields in SQL can lead to a "datatype mismatch" error, so it’s important to format the SQL query correctly depending on whether the field is numeric or text.

If rst!StatusCodeID <> CLng(DLookup("[StatusCodeID]", "ref_StatusCode", "[STATUS_CODE] = " & rstLocal!STATUS_CODE)) Then
    rst.Edit
End If