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 fields 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 |
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