Good day everyone
In my table, there are 3 columns I want to concatenate in a Query. These columns are named LEO1, LEO2, and LEO3 which are the Initial and Surname of a person.
In the query I create a new column LEO with the following string:
LEO: IIf(IsNull([LEO1]),"",[LEO1] & ", ") & IIf(IsNull([LEO2]),"",[LEO2] & ", ") & IIf(IsNull([LEO3]),"",[LEO3])
LEO1 will always be selected. If the second person joins their name will be selected as LEO2 and the same with 3. The problem I have is if LEO2 is blank and LEO3 is blank, it leaves the ',' after LEO1 and if LEO1 en LEO2 is selected, it leaves the ',' after LEO2 which I don't want to see when I view reports.
So when I view the query it will look like this LEO1, LEO2, instead of LEO1, LEO2
Is there another way to concatenate the query to avoid the ',' either LEO2 or LEO3 is left blank?
In my table, there are 3 columns I want to concatenate in a Query. These columns are named LEO1, LEO2, and LEO3 which are the Initial and Surname of a person.
In the query I create a new column LEO with the following string:
LEO: IIf(IsNull([LEO1]),"",[LEO1] & ", ") & IIf(IsNull([LEO2]),"",[LEO2] & ", ") & IIf(IsNull([LEO3]),"",[LEO3])
LEO1 will always be selected. If the second person joins their name will be selected as LEO2 and the same with 3. The problem I have is if LEO2 is blank and LEO3 is blank, it leaves the ',' after LEO1 and if LEO1 en LEO2 is selected, it leaves the ',' after LEO2 which I don't want to see when I view reports.
So when I view the query it will look like this LEO1, LEO2, instead of LEO1, LEO2
Is there another way to concatenate the query to avoid the ',' either LEO2 or LEO3 is left blank?
