hiding "text" for null values

[fishqqq]

I have a form with an unbound text field that has it's control source
set as follows:

=[Desc] & " Class " & [Class] & " UN" & [UN] & " PG" & [PG] & " - " &
[Lock QTY] & " @ " & [Lock L] & " x " & [Lock W] & " x " & [lock h] &
" " & [Lock Dims Unit of Measurement] & " @ " & Round([pcs wgt kgs],2)
& " kgs"

which returns...

BATTERY Class 8 UN1719 PGII - 6 @ 20 x 20 x 20 Inches @ 9.07 kgs (when
working correctly)

and

Class UN PG - 20 @ 10 x 10 x 10 Inches @ 45.36 kgs (when not working
correctly)

I would like to know if there is a way to hide "Class", "UN" &
"PG" if the [Desc],[Class], [UN] & [PG] fields are null?
Otherwise this is confusing to the user who views this info later.

I appreciate any help on this formatting issues
Steve

 

[John W. Vinson]

I would like to know if there is a way to hide "Class", "UN" &
"PG" if the [Desc],[Class], [UN] & [PG] fields are null?
Otherwise this is confusing to the user who views this info later.

Yes. You can use a somewhat obscure trick: both the & and + operators
concatenate strings, but & treats a NULL as a zero length string and + returns
NULL if either argument is NULL. You can put the text literals that you want
to vanish inside parentheses and use + so that the concatenated string becomes
NULL if the field value is null:

=[Desc] & (" Class " + [Class]) & (" UN" + [UN]) & (" PG" + [PG]) & " - " &
[Lock QTY] & " @ " & [Lock L] & " x " & [Lock W] & " x " & [lock h] &
" " & [Lock Dims Unit of Measurement] & " @ " & Round([pcs wgt kgs],2)
& " kgs"
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Michael J. Strickland]

Instead of using:
" Class " & [Class]

Use:

IIf(IsNull([Class]),""," Class: ") & Nz([Class],"")

and so on for the other labels/fields

 

[fishqqq]

Instead of using:
" Class " & [Class]

Use:

IIf(IsNull([Class]),""," Class: ") & Nz([Class],"")

and so on for the other labels/fields

--
Mike

-----------------------------------------------------------------------
Michael J. Strickland
Quality Services                           (e-mail address removed)
703-560-7380
-----------------------------------------------------------------------

I have a form with an unbound text field that has it's control source
set as follows:

=[Desc] & " Class " & [Class] & " UN" & [UN] & " PG" & [PG] & " - " &
[Lock QTY] & " @ " & [Lock L] & " x " & [Lock W] & " x " & [lock h] &
" " & [Lock Dims Unit of Measurement] & " @ " & Round([pcs wgt kgs],2)
& " kgs"

which returns...

BATTERY Class 8 UN1719 PGII - 6 @ 20 x 20 x 20 Inches @ 9.07 kgs (when
working correctly)

Class  UN PG - 20 @ 10 x 10 x 10 Inches @ 45.36 kgs (when not working
correctly)

I would like to know if there is a way to hide "Class", "UN" &
"PG"     if the [Desc],[Class], [UN] & [PG] fields are null?
Otherwise this is confusing to the user who views this info later.

I appreciate any help on this formatting issues
Steve

Thanks Michael, problem fixed!