Concatenating with and without comma

[Owl]

I want the comma to appear ONLY when there is an appartment address AND a
street address. I can't seem to get rid of the comma when there is only a
street address. I have tried to adapt what I have read on Discussions, but
have not been successful.

My code is the following:

Address: Trim(IIf(Len([BldgAddress] & "")=0,"",[BldgAddress] & ", " &
[StAddress]))

Record 1's BldgAddress is 1A Goble Mansions.
Record 1's StAddress is 45 Goble Road.

Record 2 doesn't have a BldgAddress.
Record 2's St Address is 53B Goble Road.

Record 3 doesn't have a BldgAddress.
Record 3's St Address is 83 Maple Road.

Thank you for any help.

 

[Jeff Boyce]

It sounds like you are saying that you want [BldgAddress]
(?apartmentaddress?) and ", " & [StreetAddress] if [BldgAddress] is not
empty ... otherwise, you want only [StreetAddress].

That sounds like an IIF() statement to me (IIF(something is true, do this,
otherwise do that))

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi,

You are almost there. Try:

Address: IIf(Trim([BldgAddress]) & ""="","",Trim([BldgAddress]) & ", ") &
Trim([StAddress])

Clifford Bass

 

[Owl]

Wow! That was firstly so quick, and secondly, JUST what I needed! It works
perfectly. However, I tried to read its logic, but couldn't make it out.
Please could you explain it to me. Thank you so much Clifford.

Hi,

You are almost there. Try:

Address: IIf(Trim([BldgAddress]) & ""="","",Trim([BldgAddress]) & ", ") &
Trim([StAddress])

Clifford Bass

I want the comma to appear ONLY when there is an appartment address AND a
street address. I can't seem to get rid of the comma when there is only a
street address. I have tried to adapt what I have read on Discussions, but
have not been successful.

My code is the following:

Address: Trim(IIf(Len([BldgAddress] & "")=0,"",[BldgAddress] & ", " &
[StAddress]))

Record 1's BldgAddress is 1A Goble Mansions.
Record 1's StAddress is 45 Goble Road.

Record 2 doesn't have a BldgAddress.
Record 2's St Address is 53B Goble Road.

Record 3 doesn't have a BldgAddress.
Record 3's St Address is 83 Maple Road.

Thank you for any help.

 

[Owl]

Actually Clifford, to save you some explanation, it is just the = in double
quotes that I don't understand.

Hi,

You are almost there. Try:

Address: IIf(Trim([BldgAddress]) & ""="","",Trim([BldgAddress]) & ", ") &
Trim([StAddress])

Clifford Bass

I want the comma to appear ONLY when there is an appartment address AND a
street address. I can't seem to get rid of the comma when there is only a
street address. I have tried to adapt what I have read on Discussions, but
have not been successful.

My code is the following:

Address: Trim(IIf(Len([BldgAddress] & "")=0,"",[BldgAddress] & ", " &
[StAddress]))

Record 1's BldgAddress is 1A Goble Mansions.
Record 1's StAddress is 45 Goble Road.

Record 2 doesn't have a BldgAddress.
Record 2's St Address is 53B Goble Road.

Record 3 doesn't have a BldgAddress.
Record 3's St Address is 83 Maple Road.

Thank you for any help.

 

[Clifford Bass]

Hi,

If may help when you know that the concatenation (&) operator has a
higher priority than the equals operator. That is, & is performed before =.
So you can read it:

Compute the value: Trim([BldgAddress]) & "". Then compare that
computed value to "".

Is that clear now?

Clifford Bass

Actually Clifford, to save you some explanation, it is just the = in double
quotes that I don't understand.

Hi,

You are almost there. Try:

Address: IIf(Trim([BldgAddress]) & ""="","",Trim([BldgAddress]) & ", ") &
Trim([StAddress])

Clifford Bass

 

[Owl]

It helps a little, but I still don't understand what the equals means in that
context and i don't know why it is in double quotes.

Hi,

If may help when you know that the concatenation (&) operator has a
higher priority than the equals operator. That is, & is performed before =.
So you can read it:

Compute the value: Trim([BldgAddress]) & "". Then compare that
computed value to "".

Is that clear now?

Clifford Bass

Actually Clifford, to save you some explanation, it is just the = in double
quotes that I don't understand.

Hi,

You are almost there. Try:

Address: IIf(Trim([BldgAddress]) & ""="","",Trim([BldgAddress]) & ", ") &
Trim([StAddress])

Clifford Bass

 

[Clifford Bass]

Hi,

The equals in that context, within the IIf() function, is telling it to
do a comparison of equality. The double quotes ("") is what is called a
zero-length string. Note that this is different from Null, which is the lack
of any value. So if we take it all apart, you have Trim([BldgAddress]). If
BldgAddress is null, you will get Trim(Null), which returns Null. Now if you
have a Null comparison in the IIf() function, it always returns the third
parameter; the false part. This is sort of a compromise because you never
know if an unknown value compared to a known value is true or false. So
IIf(Null = "", "A", "B") will always return "B". Now if you have a
zero-length string in BldgAddress, or if you have only spaces, say " ",
Trim([BldgAddress]) will return a zero-length string (""). So you will get
IIf("" = "", "A", "B") which will return "A". In one case where there is no
BldgAddress you get "B" and in the other you get "A". How do you resolve
that? The answer: you convert the Null into a known value. You would want
to change it into a zero-length string because that is what you get for
Trim([BldgAddress]) when BldgAddress is a string of zero or more characters.
How to do that? Oddly in a lot of databases, an unknown value concatenated
with a known value returns that known value. Theoretically it should not,
but should return Null. Be that as it may, you can make use of that odd fact
and use Null & "" which will return "". Now, you have taken care of both of
those types of situations. So when BldgAddress contains "45 Goble Road" you
have IIf("45 Goble Road" & "" = "", "", "45 Goble Road" & ", "). Which would
become IIf("45 Goble Road" = "", "", "45 Goble Road, "). "45 Goble Road" is
not equal to "" so you get the false part which is "45 Goble Road, ". When
BldgAddress is blank you have either IIf(Null & "" = "", "", Null & ", ") or
IIf("" & "" = "", "", "" & ", "). Both of these become IIf("" = "", "", ",
"). "" is equal to "", so you get the true part, which is just "".

How's that?

Clifford Bass

 

[Clifford Bass]

Hi,

One final thing. As seen in my prior post, the equals symbol is not
within the quotes, it is outside of them. As you read along, when you come
to the first quote symbol, it starts a string. That string goes until a
second quote symbol is encontered. If you then encounter a third quote
symbol that starts another string. And then the fourth ends the string. So
odd-numbered quote symbols start strings and even-numbered quotes end
strings. There is one exception to that and that is when you have an
even-numbered quote immediately followed by an odd-numbered quote. In that
case it is interpreted as the actual quote character within the string. So
you might have this:

strSentence = "He asked: ""What is this?"" of the old lady."
MsgBox strSentence

Which will show:

He asked: "What is this?" of the old lady.

Hope that helps,

Clifford Bass

 

[Owl]

Hi Clifford

Thank you very much for your patience and your excellent explanations. I
really appreciate it.

It is late here (in South Africa) and sleep has hit me like a sledge hammer,
but I think I got about 99.9% of the first explanation and about 95% of the
second. I will read them both again when I am awake tomorrow. I absolutely
LOVE this website. I read it even when I am not looking for something in
particular - then it is for pure unadulated pleasure, excitement and delight.

 

[Clifford Bass]

Hi,

You are very welcome! Yes, a new day with a rested brain will help.
And good luck to you!

Clifford Bass

 

[Owl]

You are so right! I am awake now and first reading was SO clear! Last night
while I was so tired, I was reading the equal signs as being inclosed as 2
quotes on either side and that the equals meant something other than equals
as a result. I picked up that you said that wasn't the case, but I didn't
get what was the case. Now I see that ""="" is simply empty string = empty
string and I see from you explanation why.

Thank you so much for all your trouble. I really appreciate it. Knowing
why something is the way it is prevents having to ask too many times for
help.

 

[Clifford Bass]

Hi,

You are very welcome! Glad to help.

Clifford Bass