Label Formating

[duecesup]

Hopefully this is an easy one.

I have created labels using a report. I have formatted the labels to display all records in thist way

SDI-8216-2007-28
STP 6 Level:0-0cm
Chipped Stone\Debitage\Metavolcanic
Qty: 1 WT: 3.5 g

The Problem happens when one of these fields is not present. If STP and /or 6 is not present in the data then the entire lable shifts right.


SDI-8216-2007-28
Level:0-0cm
Chipped Stone\Debitage\Metavolcanic
Qty: 1 WT: 3.5 g

I need the formating to stay exactley the same. Is there a way to put in buffers or an input mask that will save the correct formating when data is not present?

My label design is as follows

=Trim([California Designated Site Number] & "-" & [Year of Excavation] & "-" & [Catalog Number])
=Trim([Recovery Type] & " " & [Unit Number] & " Level: " & [Level] & " cm")
=Trim([Material Class] & " \" & [Object] & " \" & [Material])
="Qty: " & [Qty] & " Wt: " & [Wt] & " g"

Thanks

 

[ezfriend]

Easy way?

Check to see if your data exists. If not, insert spaces into the label so that you have enough indentations.
Oh, and for this scenario, don't trim the data

 

[wazz]

the Nz (nulls) function allows you to substitute a value.

if [Level] is null then Nz([Level]) will substitute 0 or "", depending on the data type.
or you can supply a value: Nz([Level], 12) or Nz([Level], "n/a"), depending on the data type.