Using concatenations in Access

[yes sir]

I am trying to get a combination of four different fields to display correctly when one or multiple fields do not have a value.



The field names are:
City
State
High School
Prep School

So when all fields have a value it will look as so: City, State/ High School/ Prep School. However, when a field is empty I want the punctuation before the value to not show.

Example: If City field doesn't have a value I want it to show as: State/ High School/ Prep School. Not as: ,State/ High School/ Prep School.

Example: If City and State don't have values then I want it to show: High School/ Prep School. Not as: /High School/ Prep School.

Example: If City, State, and High School are empty then I want it to show as: Prep School. Not as: /Prep School.


If anyone could help or simply just give me a link so I can learn how to do this on my own. This is probably easy stuff for most of you, but I have had help before on this but still don't quite understand how to write up the expression. I know you use the IIf, but I don't really understand what it means.

Thanks

[RuralGuy]

Actually if you use the "+" instead of the normal Access string concatenation character of "&" then the null fields will not show. Does that give you some ideas?

[NoellaG]

HI,

if you want to use the iif (immediate if) function, the syntax is:

Iif(test,value if true, value if false)

So, in your case the test can be :

if the field contains a value (lenght > 0),

the value if true is :

the field contant + "/",

if the length is not > 0 the value is :

an empty string,

and the complete expression could be:

Iif(len([MyFieldname]) >0,[MyFieldName] & "/","")

gr
NG

[yes sir]

Here is what I ended up using:

=IIf(IsNull([City]) Or [City]="","",("" & [City] & ", "))+IIf(IsNull([State]) Or [State]="","",("" & [State]))+IIf(IsNull([High School]) Or [High School]="","",("/ " & [High School])) & IIf(IsNull([Prep School]) Or [Prep School]="","",("/ " & [Prep School]))

However, using this expression, if a user only enters the City, HS, and Prep School. Then it would look like this: City, / High School/ Prep School. However, I assume, that if the user is entering the City, then more than likely they would enter the State for that city, so I doubt this would be a problem. If anyone knows another way I could go about this to where this is avoided please share.

Thanks for your help

[RuralGuy]

If you would, try this expression for me please:
([City] + ", ") & ([State] + "/ ") & ([High School] + "/ ") & [Prep School]

[yes sir]

If you would, try this expression for me please:
([City] + ", ") & ([State] + "/ ") & ([High School] + "/ ") & [Prep School]

That works just the same. Thank you sir.

[RuralGuy]

You're welcome and thanks for giving it a try.

[yes sir]

Just realized what I was using wasn't working correctly, but this expression is on the money: =([City]+", ") & ([State]) & "/ "+([High School]) & "/ "+([Prep School])

[RuralGuy]

Excellent! Glad to hear you got it sorted.

[TheShabz]

just to add, be really careful about using + to concatenate. if you try to concatenate two numbers with a + you'll just add them together instead. I try to avoid using + to concatenate and just use the nz() function to take care of nulls.

[RuralGuy]

"+" propagates Nulls and "&" does not. It can be useful sometimes as in the example I provided in Post #5.

[TheShabz]

I agree that it has its benefits, just wanted to point out a caveat.

[pbaldy]

I'm with Allan. I've always loved the simplicity of using + in these types of situations. These apparently return the same thing; one is about 4 times longer than the other:

=IIf(IsNull([City]) Or [City]="","",("" & [City] & ",  "))+IIf(IsNull([State]) Or [State]="","",("" &  [State]))+IIf(IsNull([High School]) Or [High School]="","",("/ " &  [High School])) & IIf(IsNull([Prep School]) Or [Prep  School]="","",("/ " & [Prep School]))
=([City]+", ") & ([State]) & "/ "+([High School]) & "/ "+([Prep School])

I'm lazy; I choose the short one.

[RuralGuy]

And I'm more lazy than Paul and can't type either so you know from where I come.