I am concatenating 2 values in a MS Access Report: "Method" and "Experiment" and they are to be separated by a comma (,).

I have figured out how to concatenate succesfully when the second value is missing (Experiment), but I am struggling to omit a blank space and comma if the first value ("Method") is missing.

The code I am using is: =([Method] & (", "+[Experiment]))

An example of my table What I would like to obtain is:

  1. Harvard, Mixed sequence
  2. Intensity
  3. Bradford
  • Use IIF() function. – Akina Apr 7 '20 at 17:37
  • Discussion here. You might want to consider a more standards compliant engine? – Vérace Apr 8 '20 at 6:05

Thank you Akina for your suggestion.

As I am really a newbie, it took me some time to work out how to use IFF.

At the end here's the working string to remove comma and space at the beginning if the first value is null (empty):

=([Method].value) & iif (IsNull([Method].value) ,"",", ") & + [Experiment]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.