DLookUp help needed with Null Value

[TriniGal]

Hello,

I have the following code and it works great. I was wondering if it's possible to tweek it a little further to add [R_FOUND], which is a date/time field where it is null.

Private Sub LOCATION_BeforeUpdate(Cancel As Integer)

Dim varADDRESS As Variant
    
    If Me.NewRecord Then
    
        varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" & Me.ADDRESS _
            & "' and [STREET] = '" & Me.LOCATION _
            & "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY & "'")
            
        If Not IsNull(varADDRESS) Then
        
            If MsgBox("This record already exists." & _
                        "Do you want to cancel these changes and go to that record instead?", _
                        vbQuestion + vbYesNo, _
                        "Duplicate Address Found") _
                        = vbYes _
            Then
                Cancel = True
                Me.Undo
                Me.Recordset.FindFirst "[ADDRESS] = '" & varADDRESS & "'"
                bolCheckDuplicate = True
            End If
            
        End If
        
    End If
    
Exit_LOCATION_BeforeUpdate:
    Exit Sub

Err_LOCATION_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_LOCATION_BeforeUpdate
    
    
End Sub

Basically I want the existing address to show up ONLY if the R_FOUND field is NULL. I've been playing around with the code and have been unsuccessful which leads me to question, if what I'm trying to do even possible?

Thanks in advance for your time.

 

[randy700]

How about?

varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" & Me.ADDRESS _
            & "' and [STREET] = '" & Me.LOCATION _
            & "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY [b][red]_
            & " and IsNull(#" & Me.R_FOUND & "#"[/red][/b])

Randy

 

[TriniGal]

Randy,

Thank you for your response. I'm getting a syntax error here

 varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" & Me.ADDRESS _
            & "' and [STREET] = '" & Me.LOCATION _
            & "' and [S_COMMUNITY] = '" & Me.S_COMMUNITY _
            & "' and IsNull(#" & Me.R_FOUND & "#"))

Any other suggestions?

 

[randy700]

Yeah,
Change this:

& "' and IsNull(#" & Me.R_FOUND & "#"))

to this:

& "' and Trim('" Me.R_FOUND & " ") = "" & "')")

Randy

 

[TriniGal]

Randy,

Now I'm getting a Compile error: Expected: end of statement

 varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", "[ADDRESS] = '" & Me.ADDRESS _
            & "' And [STREET] = '" & Me.LOCATION _
            & "' And [S_COMMUNITY] = '" & Me.S_COMMUNITY _
            & "' And Trim('" & Me.R_FOUND & " ") = "" & "')")

Where the last closing bracket is highlighted.

Any ideas?

 

[PWise]

how about

 &  " and r_found is null "

 

[PWise]

sb

 varADDRESS = DLookup("[ADDRESS]", "LEAKS FOUND", 
             "[ADDRESS] = '" & Me.ADDRESS _
            & "' And [STREET] = '" & Me.LOCATION _
            & "' And [S_COMMUNITY] = '" & Me.S_COMMUNITY _
            & "'  and r_found is null " )