Concatenated City and State

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

I have two fields to specify a construction job location,
[LocationCity] and [LocationState].

I would like to execute the following report print
behavior:

Condition Print Example
------------------------------ ------------------------
City and State both entered "Philadelphia, PA"
City only entered "Philadelphia"
State only entered Nothing
Neither City nor State entered Nothing

If a field is Null, or contains a zero-length string or a
string composed only of spaces, I consider it not entered.

I constructed an IIF statement using Nz function calls,
but some records are printing "," only. Thinking these
might contain spaces, I explicitly changed them to Null
with an update query, but still get the same result.

Can anyone determine where I've gone wrong?

Thank you.

Kevin Sprinkel
Becker & Frondorf
 
-----Original Message-----
How about ...

City & (", " + State)
Click to expand...

Thanks for your reply.

This would print "," if City and State are blank, null, or
contain only spaces, though, wouldn't it?

Kevin Sprinkel
 
I'd deleted it. In recreating it, I tried the following,
which gives the result I want.

Thanks for your response.

=IIf(Nz([LocationCity])="","",[LocationCity] & ", " &
[LocationState])

Kevin Sprinkel
Becker & Frondorf
 
-----Original Message-----
Oops! Replace the & with a +

City + (", " + State)
Click to expand...

Thanks, Cheryl. Compact AND effective...2 improvements
over my expression!

Best regards.

Kevin Sprinkel
Becker & Frondorf
 
Kevin,

Difficult to say where you've gone wrong, since we don't know where
you've gone :-)

- Steve Schapel, Microsoft Access MVP
 
Kevin,

Cheryl's suggestion would work as you require if State is null, and if
both City and State are null, but not if only the state is entered.
For that, you would need...
IIf(IsNull([City]),Null,[City] & (", " +[State]))

AFAIK, it is not possible to have "only spaces" in a field in Access.
So that only leaves the possibility of zero-length string. Is this a
real possibility? If so, how are they coming into the picture?

- Steve Schapel, Microsoft Access MVP
 
Kevin, Cheryl,

Maybe I've missed something here, but doesn't this result in nothing
returned if there is a City entered but no State?

- Steve Schapel, Microsoft Access MVP
 
Kevin,

I believe this will return:
Philadelphia,
in the case of City but no State entered?

By the way, I believe Nz([LocationCity])="" will always evaluate to
False. Unless, that is, you specify the 'if null' value to use as "",
i.e. Nz([LocationCity],"")=""

- Steve Schapel, Microsoft Access MVP
 
The best way I know of to check for Nulls, zero-length strings or blanks is:

=IIf(Len(Trim$([LocationCity] & "")) = 0, "", [LocationCity] & _
IIf(Len(Trim$([LocationState] & "")) = 0, "", ", " & [LocationState])

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Kevin Sprinkel said:
I'd deleted it. In recreating it, I tried the following,
which gives the result I want.

Thanks for your response.

=IIf(Nz([LocationCity])="","",[LocationCity] & ", " &
[LocationState])

Kevin Sprinkel
Becker & Frondorf

-----Original Message-----
Kevin,

Could you post the IIF statement?

-Andy
Click to expand...
Click to expand...
 
By the way, I believe Nz([LocationCity])="" will always
evaluate to
False. Unless, that is, you specify the 'if null' value to use as "",
i.e. Nz([LocationCity],"")=""
Click to expand...


Thanks, Steve.
 
AFAIK, it is not possible to have "only spaces" in a
field in Access.
So that only leaves the possibility of zero-length string. Is this a
real possibility? If so, how are they coming into the
Click to expand...
picture?

I'm not sure they are present, only that my early IIF
statement produced several instances of ", ". I now
understand my logic error--Nz([LocationCity],"") returned
a blank string if the field was null.

Thanks for your help.
 
-----Original Message-----
The best way I know of to check for Nulls, zero-length strings or blanks is:

=IIf(Len(Trim$([LocationCity] & "")) = 0, "", [LocationCity] & _
IIf(Len(Trim$([LocationState] & "")) = 0, "", ", " &
Click to expand...
[LocationState])

Thanks, Doug, for the new arrow in the quiver. :^)

Kevin Sprinkel
Becker & Frondorf
 
Back
Top