Form report - IF statement query

[john141]

I have created a form called ProductLabels. This form is filled out and it produces the following report which is printed as a label.

The part in question is the Serial / Batch. This line is :

=Trim([Forms].[Frm_ProductLabels].[part_no]) & "/" & [Forms].[Frm_ProductLabels].[Text16]

part_no = part number pulled from the below query of my database table. Text16 is a textbox on the form, where the user enters the serial number.

On the report label, it is then printed as "part_no / serial no"

This works great, however not all equipment have a part number. This means when there is no part number it just displays as "/ serial number". What i would like to do is in this case, take away the / and have the serial number by itself.

How could i go about doing this? I was thinking of having an IF statement in my query saying IF part_no is not null then "part_no /" else "blank".

I am not sure how to produce this though, any help would be great

 

[pbaldy]

Try

=(Trim([Forms].[Frm_ProductLabels].[part_no]) + "/") & [Forms].[Frm_ProductLabels].[Text16]

 

[john141]

Try

=(Trim([Forms].[Frm_ProductLabels].[part_no]) + "/") & [Forms].[Frm_ProductLabels].[Text16]

That works perfectly, had no idea it would be that simple! Makes sense now I am looking at it though

Thank you

 

[pbaldy]

Happy to help! It takes advantage of the fact that + will propagate Null and & will not.

 

[arnelgp]

Trim() is excess there. if part number is not blank, any leading space will be automatically removed
by msa.

 

[Gasman]

Trim() is excess there. if part number is not blank, any leading space will be automatically removed
by msa.

Isn't it for trailing spaces as well?

tt="Paul   "
? tt
Paul   
? len(tt)
 7 
? len(tt + "/")
 8 
? len(trim(tt) + "/")
 5

 

[arnelgp]

correction on post#5, its trailing space (not leading).
part_no is a Field in the table.
try adding trailing space to the field, access will remove them.