asked on

Merging data only if not null

hi!

im trying to merge data from 20 columns into one column with "and" or a comma as a separator. unfortunately, i keep on getting the null values as well.

ex. column 1 - null
column 2 - null
column 3 - gate

merge into one column it looks like - "and and gate" or ", , gate"

how can i make access return the value only if it is not null - for twenty columns, i do not think it is practical to create if statements for all the possibilities. please help!!!!!

thanks,
ann

ASKER CERTIFIED SOLUTION

ChrisFretwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

if you want like: ABC,n/a,CBS,n/a use:

Nz(Field1,"n/a") & ", " & Nz(Field2,"n/a") & ", " & Nz(Field3,"n/a") & ", " & Nz(Field4,"n/a")

Mike Eghtebas

But, if you like ABC,CBS and ignoring null values, then use

Dim strTemp As String
if Nz(Field1,"") <>"" Then strTemp =strTemp &Field1 & ", "
if Nz(Field2,"") <>"" Then strTemp =strTemp &Field2 & ", "
if Nz(Field3,"") <>"" Then strTemp =strTemp &Field3 & ", "
if Nz(Field4,"") <>"" Then strTemp =strTemp &Field4 & ", "

strTemp = Left(strTemp,Len(strTemp)-2)

Mike Eghtebas

FYI: and empty field, depending in its field type could be Null or Empty. Therefore we need to use following code to handle both:

Nz(Field1,"") <>""

Now, Field1 if it is and Empty string (""), Nz() will become "" and then will be compared with <>"".
But, if it is a null value, it will change to "" thus covering both possiblities.

Therefore, first code need to be:

iif(Nz(Field1,"")="","n/a", Field1) & ", " & iif(Nz(Field2,"")="","n/a", Field2) & ", " & ...

Also, you may consider using | (in my keyboard it is above Enter key).

mike