NZ(DLookup) changed from "Number" field to "Text" field 1

[BigDave619]

So I made this DB with a lot of help about 15 years ago and haven't done any programming since. DB has worked great but I just had to make a change and lost an important feature. On the following NZDlookup "KnoxNumber" I had to change to a text property from a number property in the table. Now this little string won't work. I'm sure it has something to do with the " ' " or something like that, but I just can't figure it out. Thank you for any help.

Private Sub Add____AfterUpdate()
Me.Address = Nz(DLookup("Address", "tblActivities", "KnoxNumber = " & [Knox File No.] _
& " And EntityNumber = " & [Ent. #] & "" _
& " And LocationNumber = " & [Add. #] & ""), Null)
Me.Zip_Code = Nz(DLookup("ZipCode", "tblActivities", "KnoxNumber = " & [Knox File No.] _
& " And EntityNumber = " & [Ent. #] & "" _
& " And LocationNumber = " & [Add. #] & ""), Null)
End Sub

 

[dhookom]

I'm not sure why this was posted in the SQL Server Programming forum when it should have been in an Access forum. However, try:

Private Sub Add____AfterUpdate()
   Me.Address = Nz(DLookup("Address", "tblActivities", "KnoxNumber = [highlight #FCE94F]'[/highlight]" & [Knox File No.] _
     & "[highlight #FCE94F]'[/highlight] And EntityNumber = " & [Ent. #] & "" _
     & " And LocationNumber = " & [Add. #] & ""), Null)
   Me.Zip_Code = Nz(DLookup("ZipCode", "tblActivities", "KnoxNumber = [highlight #FCE94F]'[/highlight]" & [Knox File No.] _
     & "[highlight #FCE94F]'[/highlight] And EntityNumber = " & [Ent. #] & "" _
     & " And LocationNumber = " & [Add. #] & ""), Null)
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BigDave619]

I wasn't sure where to post but thank you. That worked great.

 

[Andrzejek]

Consider changing your code to:

Private Sub Add_AfterUpdate()[blue]
Dim strWhere As String

strWhere = "KnoxNumber = '" & [Knox File No.] & "' " _
     & " And EntityNumber = " & [Ent. #] _
     & " And LocationNumber = " & [Add. #] [/blue]
[green]
'Debug.Print strWhere[/green]

   Me.Address = Nz(DLookup("Address", "tblActivities", [blue]strWhere[/blue]), Null)
   Me.Zip_Code = Nz(DLookup("ZipCode", "tblActivities", [blue]strWhere[/blue]), Null)
End Sub

Just a suggestion...

Edit: Thanks Duane, I added some )

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I like the way Andy thinks and uses TGML for readability. Much easier to maintain. However a couple ")" are missing. I'm a little confused why you would use Nz() which replaces Null with another value. Your usage suggests if the DLookup() is Null, make it Null. Typically you would use something like
[pre]Nz([Expression],"NA")[/pre]
or
[pre]Nz([Expression],0)[/pre]

   Me.Address = Nz(DLookup("Address", "tblActivities", strWhere[highlight #FCE94F])[/highlight], Null)
   Me.Zip_Code = Nz(DLookup("ZipCode", "tblActivities", strWhere[highlight #FCE94F])[/highlight], Null)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016