Error when trying to set variable to Dlookup results ...

[Phonesa]

if the results are not found. How can I set it up so that it does not give me "invalid use of null" error?

My code looks like this:

If Not IsNull(cbAddress) And cbAddress <> "N/A" Then
Dim strState As String
Dim strCity As String
Dim strZip As String
strCity = DLookup If Not IsNull(cbAddress) And cbAddress <> "N/A" Then
Dim strState As String
Dim strCity As String
Dim strZip As String
strCity = DLookup("City", "DefendantAddress", "DefendantID ='" & txtDefendantID & "'" & _
" AND Address = '" & cbAddress & "'")
strState = DLookup("City", "DefendantAddress", "DefendantID ='" & txtDefendantID & "'" & _
" AND Address = '" & cbAddress & "'")
strZip = DLookup("City", "DefendantAddress", "DefendantID ='" & txtDefendantID & "'" & _
" AND Address = '" & cbAddress & "'")
Me.txtAddress = strCity & ", " & strState & " " & strZip
End If

 

[AncientOne]

Aside from the fact that your syntax is "dodgy", this doesn't look to be the optimum method of returning an address. What are you trying to do, Phonesa, and why are you doing it this way?

 

[WayneRyan]

phonesa,

Dim strState As String 
Dim strCity As String 
Dim strZip As String 

If Not IsNull(cbAddress) And cbAddress <> "N/A" Then 
   strCity = DLookup("City", _
                     "DefendantAddress", 
                     "DefendantID ='" & txtDefendantID & "' And " & _ 
                     "Address = '" & cbAddress & "'")
   If IsNull(strCity) Then
      MsgBox("No such Defendant"
      Exit Sub
   End If 
   strState = DLookup("City", _
                      "DefendantAddress", _
                      "DefendantID ='" & txtDefendantID & "' And " & _ 
                      "Address = '" & cbAddress & "'") 
   strZip = DLookup("City", _
                    "DefendantAddress", _
                    "DefendantID ='" & txtDefendantID & "' And " & _ 
                    "Address = '" & cbAddress & "'") 
   Me.txtAddress = strCity & ", " & strState & " " & strZip 
End If

Wayne

 

[Phonesa]

I have an unbound control, txtAddress which I would like to populate depending on my lookup. I'm currently working on a database where each Defendant can have many addresses. They could have 5 different citations against them and all the addresses could be different. I store just the Address(no state/city/zip) in my Citation table. I'm converting a really, really, really, really old database to access and the data is very inconsistent. They can have addresses with a state or zip and no city or an address with a state but no city, etc.

 

[Phonesa]

Wayne, I am still getting the same error I'm afraid. When it breaks, it still highlights the same area. I think it's because it's trying to set strCity = NULL

If Not IsNull(cbAddress) And cbAddress <> "N/A" Then
strCity = DLookup("City", _ <----------------------
"DefendantAddress", _
"DefendantID ='" & txtDefendantID & "' And " & _
"Address = '" & cbAddress & "'")


Anyway I can check and see if my lookup returns something before I try to set them to my string variables?

 

[WayneRyan]

phonesa,

Dim strState As String 
Dim strCity As String 
Dim strZip As String 

If Not IsNull(cbAddress) And cbAddress <> "N/A" Then 
   strCity = Nz(DLookup("City", _
                     "DefendantAddress", 
                     "DefendantID ='" & txtDefendantID & "' And " & _ 
                     "Address = '" & cbAddress & "'"), "")
   If IsNull(strCity) Then
      MsgBox("No such Defendant"
      Exit Sub
   End If 
   strState = Nz(DLookup("City", _
                      "DefendantAddress", _
                      "DefendantID ='" & txtDefendantID & "' And " & _ 
                      "Address = '" & cbAddress & "'"), "") 
   strZip = Nz(DLookup("City", _
                    "DefendantAddress", _
                    "DefendantID ='" & txtDefendantID & "' And " & _ 
                    "Address = '" & cbAddress & "'"), "")
   Me.txtAddress = strCity & ", " & strState & " " & strZip 
End If

That will get rid of the nulls.

Wayne

 

[Phonesa]

Ah. Thanks!