Combining fields - spacing between values

[BonnieG]

Hi

I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.

I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.

I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.

Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.

IIf(IsNull([zam_adm]),"","ADM\") & [zam_adm] & " " & 
IIf(IsNull([zam_cts]),"","CTS\") & [zam_cts] & " " & 
IIf(IsNull([zam_crs]),"","CRS\") & [zam_crs]& " " & 
IIf(IsNull([zam_IPB]),"","IPB\") & [zam_IPB]& " " & 
IIf(IsNull([zam_opr]),"","OPR\") & [zam_opr]& " " & 
IIf(IsNull([zam_owl]),"","OWL\") & [zam_owl]& " " & 
IIf(IsNull([zam_pmi]),"","PMI\") & [zam_pmi]& " " & 
IIf(IsNull([zam_wl]),"","WL\") & [zam_wl]& " " & 
IIf(IsNull([zam_sgt]),"","SGT\") & [zam_sgt]

The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?

Any ideas? I've attached a screenshot of some of the output...

I'm probably doing this in a really messy way, aren't I?

Any advice appreciated!

 

[pr2-eugin]

Why would you want to combine 32 fields?

Anyway, the IsNull is failing, suggests that the field is not null but a Zero Length String..

IIf(Len([zam_adm] & '') = 0, "", "ADM\" & [zam_adm] & " ") & 
IIf(Len([zam_cts] & '') = 0, "", "CTS\" & [zam_cts] & " ") & 
IIf(Len([zam_crs] & '') = 0, "", "CRS\" & [zam_crs] & " ") & 
IIf(Len([zam_IPB] & '') = 0, "", "IPB\" & [zam_IPB] & " ") & 
IIf(Len([zam_opr] & '') = 0, "", "OPR\" & [zam_opr] & " ") & 
IIf(Len([zam_owl] & '') = 0, "", "OWL\" & [zam_owl] & " ") & 
IIf(Len([zam_pmi] & '') = 0, "", "PMI\" & [zam_pmi] & " ") & 
IIf(Len([zam_wl] & '') = 0, "", "WL\" & [zam_wl] & " ") & 
IIf(Len([zam_sgt] & '') = 0, "", "SGT\" & [zam_sgt])

If you do not want any space, you can also use Replace function.

Replace("your Spaced String", " ", "")

 

[BonnieG]

I need the output of those fields to be lumped together so they're easily readable... it makes sense to me, but realise it may not make sense to anyone else!

Thank you for your help.

 

[pr2-eugin]

Okay ! Hope you have solved your query ! Good luck !