Cascading Combo Boxes: Na Instead of Blanks - Cascading Combo Boxes in Microsoft Access


	Back to Cascading Combo Boxes Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Cascading Combo Boxes: Na Instead of Blanks

PeterPaul Derks

5 months ago

Dear all,

FYI: I am relatively new at Access and am trying my best to figure out stuff by myself first. So please bear with me, even if I am inquiring about the obvious.

In the Cascading Combo Boxes: One Box Filters Another Extended Cut a text field is put over the ComboBox ('the cheese'). To get rid of the #Error message showing, Nz is added to the code. In my database this looks as follows.

Before Nz:
=DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & [ActieCombo])

After Nz:
=DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];0))

This gets rid of the #Error message and the text field is now what I would call 'blank'. Instead of showing 'blank' I would like it to show the text "N/A". I have looked into a lot of Tech Help video's about Nz, IIF, Null, DLookup, etc. but I can't seem to figure out how to get the right code. Just one exmample:

=DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];"N/A"))

If anyone here has a solution or could put me in the right direction, it would be much appreciated.

Regards,

Peter

Lars Schindler @Reply

5 months ago

PeterPaul

This
=DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];"N/A"))
will not work because ActieID would then have to assume the value N/A, even though ActieID probably only allows long integers.

Perhaps try something along these lines
=IIF(DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];0))=0;"N/A";DLookUp("ActieNaam";“DActiesm1s0ActiesT”;"ActieID=" & Nz([ActieCombo]; 0)))

	Lars Schindler @Reply 5 months ago
Although, of course, it could be even shorter and more concise. ;-)

	Lars Schindler @Reply 5 months ago
IIF Function: https://599cd.com/blog/display-article.asp?ID=1580 NULL: https://599cd.com/blog/display-article.asp?ID=2241

	Juan Rivera @Reply 5 months ago
Try this and see =Nz(DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];0));"")

	Lars Schindler @Reply 5 months ago
Juan Definitely much shorter, but still without N/A ;-) =Nz(DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & Nz([ActieCombo];0));"N/A")

Lars Schindler @Reply

5 months ago

And of course, I still need to improve my previous cumbersome attempt.
DLookUp(...)=0 doesn't make sense, because only ActieID becomes 0, but not DLookUp itself.

A variant with IIF could look something like this:

=IIf(IsNull(DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & [ActieCombo]));"N/A";DLookUp("ActieNaam";"DActiesm1s0ActiesT";"ActieID=" & [ActieCombo]))

But Juan's version is better either way. :-)

	PeterPaul Derks OP @Reply 5 months ago
Thank you Lars and Juan, works like a charm. Very elegant solution. And now that I look at it, I am wondering why this didn't come to my mind. Much appreciated.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Cascading Combo Boxes.

Next Unseen

New Feature: Comment Live View