Error in VBA Code

[guillermoftw]

Hi everyone,

I'm using the DLookup function to look into a column to pull a value but the problem is i am receiving a runtime error 94 because not everytime will the column have "Related" or "Associated". Is there anyway to work around this error?



I have posted my code below.

Dim AdminClose As String
Dim SQL As String
AdminClose = DLookup("Type", "Admin Closures", "Type = 'Related'")

If [AdminClose] = "Related" Then
SQL = "UPDATE [WTC Related Codes] INNER JOIN [Admin Closures] ON ([WTC Related Codes].[BSOC File #]=[Admin Closures].[BSOC File #]) AND ([WTC Related Codes].[RC Code]=[Admin Closures].[DX Code]) SET [WTC Related Codes].[RC NIOSH Decision] = [Admin Closures]![Decision], [WTC Related Codes].[Denial Reason] = [Admin Closures]![Denial Reason], [WTC Related Codes].[Date of Decision] = [Admin Closures]![Date of Decision];"
DoCmd.RunSQL SQL

AdminClose = DLookup("Type", "Admin Closures", "Type = 'Associated'")
Else: [AdminClose] = "Associated"
SQL = "UPDATE [Medically Associated Codes] INNER JOIN [Admin Closures] ON ([Medically Associated Codes].[BSOC File #]=[Admin Closures].[BSOC File #]) AND ([Medically Associated Codes].[MA Code]=[Admin Closures].[DX Code]) SET [Medically Associated Codes].[MA NIOSH Decision] = [Admin Closures]![Decision], [Medically Associated Codes].[Denial Reason] = [Admin Closures]![Denial Reason], [Medically Associated Codes].[Date of Decision] = [Admin Closures]![Date of Decision];"
DoCmd.RunSQL SQL
End If

[pbaldy]

A String variable can't hold a Null, which is what the DLookup() will return if there's no value. You can change that to Variant, or wrap the DLookup() in the Nz() function.

[guillermoftw]

Hi pbaldy,

Thanks for the response. Could you explain a variant or what the Nz function does?

Thanks!

[pbaldy]

Variant is the only data type you can declare the variable as that can hold Null.

https://msdn.microsoft.com/en-us/lib...ice.11%29.aspx