String ignoring null (1 Viewer)

[fat controller]

Is there a way to create a text string that ignores nulls?

I have the following fields

Zone1
Zone2
Zone3
Zone4
Zone5
Zone6

These are set to return a number as text (so 1, 2, 3 etc), or Null as appropriate.

What I am trying to achieve is that the text stored is concatenated into a string (TextOut), so if:

Zone1 = 1
Zone2 = Null
Zone3 = 3
Zone4 = 4
Zone5 = Null
Zone6 = Null

TextOut to show 1 3 4 and ignore the nulls. How would I achieve this?

EDIT - Would IIf(Is NotNull(zone1 & zone2 [etc]) work?

 

[arnelgp]

EDIT - Would IIf(Is NotNull(zone1 & zone2 [etc]) work?

IIf( IsNull( zone1 & zone3 & ....), ...., )

 

[CJ_London]

this code will do what you want but will have a comma at the end which you will need to remove - how you do this depends on whether this is in query, form or code

iif(not isnull(zone1),zone1 & ", ","") & iif(not isnull(zone2),zone2 & ", ","") & ....

 

[MarkK]

Or in VBA, a loop . . .

dim i as integer
dim tmp as string
dim fld as dao.field

for i = 1 to 6
   set fld = rst.fields("Zone" & i)
   if not isnull(fld) then tmp = tmp & ", " & fld
next

if len(tmp) > 0 then tmp = Mid(tmp, 3)

. . . assuming these are fields in a recordset.

 

[stopher]

Not sure I fully understand the requirements particular when I see other contributors interpretations. I don't see and commas in your output so I'd go for this (untested):

Trim( (Zone1+" ")&(Zone2+" ") & (Zone3+" ") & (Zone4+" ") & (Zone5+" ") & (Zone6+" ") )

Take a look here at the nuances between & and +

 

[Accessna]

Dim i as Byte
Dim tmp as string
 
tmp = ""
For i = 1 to 6
  tmp = tmp & Nz(fields("Zone" & i),"") & _
           IIf(Nz(fields("Zone" & I),"")<>"" and i<6,", ","")
Next i

 

[fat controller]

Not sure I fully understand the requirements particular when I see other contributors interpretations. I don't see and commas in your output so I'd go for this (untested):

Trim( (Zone1+" ")&(Zone2+" ") & (Zone3+" ") & (Zone4+" ") & (Zone5+" ") & (Zone6+" ") )

Take a look here at the nuances between & and +

Sorry for the late response to this - broadband issues along with a few other things, and of course work, have all slowed me down somewhat.

Thanks to all for the input - stopher's solution above worked a treat