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?
Concatenate
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Concatenate
Hi. Try the following:
Copays: [Copay1] & "/" & IIF([Copay2] Is Null,"",[Copay2] & "/") etc. (repeating the IIF for each Copay). Andy.
Copays: [Copay1] & "/" & IIF([Copay2] Is Null,"",[Copay2] & "/") etc. (repeating the IIF for each Copay). Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- StarLounger
- Posts: 52
- Joined: 04 Feb 2010, 17:14
- Location: Brookings, South Dakota
Re: Concatenate
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])
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])
gary
Those who dance are considered insane by those who can't hear the music. - George Carlin
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Concatenate
I think this is neater than my solution. Andy.geedeearr wrote: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'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Concatenate
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.
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Concatenate
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])
Try
[Copay1] & IIf([Copay2]="", "", "/" & [Copay2]) & IIf([Copay3]="", "", "/" & [Copay3]) & IIf([Copay4]="", "", "/" & [Copay4]) & IIf([Copay5]="", "", "/" & [Copay5])
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Concatenate
Thank you - that worked perfectly.
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Concatenate
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.
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.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Concatenate
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])
[Copay1] & IIf(Nz([Copay2])="", "", "/" & [Copay2]) & IIf(Nz([Copay3])="", "", "/" & [Copay3]) & IIf(Nz([Copay4])="", "", "/" & [Copay4]) & IIf(Nz([Copay5])="", "", "/" & [Copay5])
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 627
- Joined: 03 Feb 2010, 15:02
Re: Concatenate
I just created another calculation as follows:
OP Mental Health CPays: IIf([Outpatient mental Health copays]="////","",[Outpatient Mental Health Copays])
OP Mental Health CPays: IIf([Outpatient mental Health copays]="////","",[Outpatient Mental Health Copays])