[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Merging data only if not null


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!!!!!


  • 3
1 Solution
You will need to something on each column (except the first), but only once on each column (or write a function to do it and pass

field1 + iif(isnull(field2),""," and "+field2) +  iif(isnull(field3),""," and "+field3) +  ect

This will only add the 'and' or comma if the field you are adding has a value.

Mike EghtebasDatabase and Application DeveloperCommented:
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 EghtebasDatabase and Application DeveloperCommented:
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 EghtebasDatabase and Application DeveloperCommented:
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).


Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now