Import Null Fields To ADO Recordset

[Sean Illingworth]

Hi Everyone. When I use ADO to import value form a query, Access pop up "invalid use of NULL"

The code is :

If Not IsNull(DLookup("[Affiliate code]", "querySophia", "[property_id] = " & Me![allocationProperty])) Then
If IsNull(DLookup("[Processor name]", "querySophia", "[property_id] = " & Me![allocationProperty])) Then

Dim affiliatecode As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim sqlaffiliatecode As String
Dim tempacode As String

sqlaffiliatecode = "SELECT [Affiliate code] FROM querySophia WHERE [property_id] = " & Me![allocationProperty]
affiliatecode.Open sqlaffiliatecode, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
tempacode = affiliatecode![Affiliate code]

rst.Open "sophia", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rst
.AddNew
![Affiliate] = tempacode
.Update
End With

affiliatecode.Close
Set affiliatecode = Nothing
rst.Close
Set rst = Nothing
End If
End If

I checked, in the Query "querySophia",[Affiliate code] is "AHX" and [Processor name] is empty which should be null.

How to deal with this problem? Any help will be appreciated

 

[HiTechCoach]

Which line of code generates the error?

 

[Sean Illingworth]

Which line of code generates the error?

If IsNull(DLookup("[Processor name]", "querySophia", "[property_id] = " & Me![allocationProperty])) Then

This line create the error

 

[HiTechCoach]

If it were me, I would used a modify version of the query "querySophia" for the Dlookup() that includes the following :

 Where [Processor name] Is Not Null

This way the Dlookup will not find any matches and return a Null. For what you are doing, I think this may be better programming logic.

From the help file:

If no record satisfies criteria or if domain contains no records, the DLookup function returns a Null