Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

BigDave619

Programmer
Oct 13, 2006
2
US
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
 
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:

Code:
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
 
I wasn't sure where to post but thank you. That worked great.
 
Consider changing your code to:

Code:
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...[santa]

Edit: Thanks Duane, I added some )

---- Andy

There is a great need for a sarcasm font.
 
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]

Code:
   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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top