Build a text sting and ignore empty text boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I ahve 5 text boxes on my form which I want to build a text string. This
works ok if I have a value in each but I get 'invalid use of null' if one is
null.

How do I skip Nulls when I build the string?

Bruce

Sub mymod()

a = [Forms]![frm_Prod_Fcst_Search]![t_p1]
b = [Forms]![frm_Prod_Fcst_Search]![t_p2]
c = [Forms]![frm_Prod_Fcst_Search]![t_p3]
d = [Forms]![frm_Prod_Fcst_Search]![t_p4]
e = [Forms]![frm_Prod_Fcst_Search]![t_p5]

mystring = a & b & c & d & e

MsgBox (mystring)

End Sub
 
The & operator always ignores Null. For example, in the Immediate window ...

? "a" & null & "b"
ab

Your code as posted should already be ignoring Null values. The only
situation I can see in which this code would raise an error is where *all*
text boxes are Null, which you could avoid by tagging an empty string on to
the end ...

MsgBox myString & ""

Perhaps this is not the actual code, or not the complete actual code? For
example, in the posted code, all variables are undeclared variants. Have you
perhaps omitted some variable declarations?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Bruce,

Use the Nz() function.

Sub mymod()
With [Forms]![frm_Prod_Fcst_Search]
a = Nz(![t_p1], "")
b = Nz(![t_p2], "")
c = Nz(![t_p3], "")
d = Nz(![t_p4], "")
e = Nz(![t_p5], "")
End With

mystring = a & b & c & d & e
MsgBox (mystring)
End Sub



Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top