How can I show only unique values when concatenating 2 field in an access report? The code would need to be case insensitive. Let's say that if the values are the same, the first value will be kept.

Example: I have two fields "Pot" and "Box" and I want to concatenate their values in an Access Report

  • Pot: TM, gnd, , ops, uht
  • Box: tm, gxs, uty, , UHT

What I would like to obtain would be:

  1. TM
  2. gnd,gxs,
  3. uty
  4. ops
  5. uht

With the string below:

=([Pot].value) & iif (IsNull([Pot].value) ,"",", ") & + [Box]

I can concatenate the fields, but non-unique values are showing up (TM, tm).

I wonder if the function IFF could be used in this context, but I am not sure how to implement it with the above string in my access report?

I am using MS Access 365.

Your Answer

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

Browse other questions tagged or ask your own question.