Concatenate

[matthewR]

I am using the following to concatenate 5 fields:

Copays: [Copay1] & "/" & [Copay2] & "/" & [Copay3] & "/" & [Copay4] & "/" & [Copay5]

My problem is that not all the copay fields don't have data but sometimes they do. Sometimes only Copay1 has data. The results end up with too many forward slashes. So thus - I get 20/25/// or 20////. Is there a way to do this where I wouldn't get all the slashes when there isn't any data?

[agibsonsw]

Hi. Try the following:
Copays: [Copay1] & "/" & IIF([Copay2] Is Null,"",[Copay2] & "/") etc. (repeating the IIF for each Copay). Andy.

[geedeearr]

Hi,
Hans showed me this trick some time back.

If you want to concatenate strings, but avoid spaces when one of them is empty, you can use + instead of &:

[Copay1] & ("/"+[Copay2]) & ("/"+[Copay3]) & ("/"+[Copay4]) & ("/"+[Copay5])

[agibsonsw]

Hi,
Hans showed me this trick some time back.

If you want to concatenate strings, but avoid spaces when one of them is empty, you can use + instead of &:

[Copay1] & ("/"+[Copay2]) & ("/"+[Copay3]) & ("/"+[Copay4]) & ("/"+[Copay5])

I think this is neater than my solution. Andy.

[matthewR]

Neither suggestion is working. Maybe I am doing something wrong. Sometimes Copay1 is the only one that has data.
This:
[Copay1] & ("/"+[Copay2]) & ("/"+[Copay3]) & ("/"+[Copay4]) & ("/"+[Copay5])
gives me 25//// or 25/30///

The other one

Copays: [Copay1] & "/" & IIf([Copay2]="","",[Copay2] & "/")
give me 25/ - when there is only Copay1. I had to use ="" instead of is null because that gave me 25//
For some reason it is not seeing the "" and gives me Copay2 (which is blank).
I didn't add the rest of the calculation until I can get Copay1 and Copay2 right.

[HansV]

The fact that geedeearr's suggestion doesn't work indicates that the values of Copay2 etc. aren't really empty, but zero-length strings instead. (A zero-length string looks empty, but it is not the same as null)

Try

[Copay1] & IIf([Copay2]="", "", "/" & [Copay2]) & IIf([Copay3]="", "", "/" & [Copay3]) & IIf([Copay4]="", "", "/" & [Copay4]) & IIf([Copay5]="", "", "/" & [Copay5])

[matthewR]

Thank you - that worked perfectly.

[matthewR]

I am back with another odd result.

I created several queries with the concatenated copay field. I have one query with all the group numbers and then I join the copay queries using an outer join to the group number field. I want everything from the (I will call it the) All query and just what matches from the copay queries.

Here is the odd part - when there isn't a match, instead of getting a blank, I am getting 4 slashes - ////. I don't understand why this would happen. I should just get a blank where there isn't a matching group number in the copay queries. I don't know how to get rid of the ////'s.

[HansV]

In a query with an outer join, you *do* get nulls if there is no match. The expression I posted higher up only checks for "". To make it check for both null and "", try this:

[Copay1] & IIf(Nz([Copay2])="", "", "/" & [Copay2]) & IIf(Nz([Copay3])="", "", "/" & [Copay3]) & IIf(Nz([Copay4])="", "", "/" & [Copay4]) & IIf(Nz([Copay5])="", "", "/" & [Copay5])

[matthewR]

I just created another calculation as follows:

OP Mental Health CPays: IIf([Outpatient mental Health copays]="////","",[Outpatient Mental Health Copays])