Is DLookup the correct option on this... 2

[WallT]

Private Sub ServiceZip_AfterUpdate()

Me.txtZoneID =(DLookup "ZoneID", "tblZipCode", "ZipCode = '" & [ServiceZip] & "'&quot

End Sub

Using the criteria above, how can I handle the null when "ServiceZip" has no match for "ZipCode" from my tblZipCode

On my Orders form I have a Zip code field, and a ZoneID field. After I UpDate the Zip Code field I want the ZoneID field to populate with that Zip Code's matching ZoneID if there is one. The problem I think I am having is that the Zip Code entered doesn't always have a ZoneID to match.

 

[lakb]

suggest that you use if statement first like

Me.txtZoneID =iif(DLookup("ZoneID", "tblZipCode", "ZipCode = '" & [ServiceZip] & "'&quot is null,"enter some value",dlookup("ZoneID", "tblZipCode", "ZipCode = '" & [ServiceZip] & "'&quot)



regards
Bernard

 

[WallT]

Thank you for the quick help. In the mean time I also realized that the ZipCodes InPut Mask was messin' things up for some reason. And I changed it to NZ(DLookUp). I will play with that for awhile. Thanks again.

 

[gnosis13]

I also use this in a data entry situation:

If IsNull(DLookup(blah,blah)) then
variable = InputBox("Enter variable:&quot
Else
variable = DLookup(blah, blah)
End if

That way the null triggers an input from the user. You would want lots more error detection because the user could just hit enter without entering anything, but you get the idea. You could do this with the iif as well.
A+, N+, MCP

 

[scking]

The NZ function simplifies this somewhat. In the below if the value is not found it will return an empty string. You can use whatever default value you wish.

Nz(DLookup("ZoneID", "tblZipCode", "ZipCode = '" & [ServiceZip] & "'&quot, "&quot

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[WallT]

The NZ function worked great for me after I realized the Zip Codes input mask was messin' things up. I just default to a Null.