Syntax question

  • Thread starter Thread starter Pat Walters
  • Start date Start date
P

Pat Walters

I have a field on the report that includes City, State,
Country. It is written so that if there is no State, it
will skip the state and just report City and Country.
(and omit comma after state)

=[LocationCity] & IIf(IsNull([LocationState]),"",", " &
[LocationState]) & ", " & [LocationCountry]


How do I write this so if there is NO CITY, it will omit
the comma after city and just report State and Country ??

Many thanks
 
Try:
=[LocationCity] & ", " + [LocationState] & ", " + [LocationCountry]

There is a subtle difference between the two concatenation operators:
"A" & Null results in "A"
"A" + Null results in: Null
 
The comma remains if there is no City to report, thus
example:

, California, USA

I want the comma to go away, if no city
-----Original Message-----
Try:
=[LocationCity] & ", " + [LocationState] & ", " + [LocationCountry]

There is a subtle difference between the two concatenation operators:
"A" & Null results in "A"
"A" + Null results in: Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a field on the report that includes City, State,
Country. It is written so that if there is no State, it
will skip the state and just report City and Country.
(and omit comma after state)

=[LocationCity] & IIf(IsNull([LocationState]),"",", " &
[LocationState]) & ", " & [LocationCountry]


How do I write this so if there is NO CITY, it will omit
the comma after city and just report State and Country ??

Many thanks
Click to expand...


.
Click to expand...
 
Sorry: I matched the result to your original example, and that was not what
you asked for.

Below is the function we actually use to do this kind of thing.

Paste it into a standard module, and use a text box with ControlSource of:
=ConcatFields(", ", [LocationCity], [LocationState], [LocationCountry])

Function ConcatFields(strDelim As String, ParamArray varList()) As Variant
Dim strOut As String
Dim i As Integer

For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Pat Watlers said:
The comma remains if there is no City to report, thus
example:

, California, USA

I want the comma to go away, if no city
-----Original Message-----
Try:
=[LocationCity] & ", " + [LocationState] & ", " + [LocationCountry]

There is a subtle difference between the two concatenation operators:
"A" & Null results in "A"
"A" + Null results in: Null


I have a field on the report that includes City, State,
Country. It is written so that if there is no State, it
will skip the state and just report City and Country.
(and omit comma after state)

=[LocationCity] & IIf(IsNull([LocationState]),"",", " &
[LocationState]) & ", " & [LocationCountry]


How do I write this so if there is NO CITY, it will omit
the comma after city and just report State and Country ??

Many thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks! I'll implement this.. I've seen "similar"
examples, but this looks like it will work best for my
application.

If not... I'll be back! :)

pat

-----Original Message-----
Sorry: I matched the result to your original example, and that was not what
you asked for.

Below is the function we actually use to do this kind of thing.

Paste it into a standard module, and use a text box with ControlSource of:
=ConcatFields(", ", [LocationCity], [LocationState], [LocationCountry])

Function ConcatFields(strDelim As String, ParamArray varList()) As Variant
Dim strOut As String
Dim i As Integer

For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The comma remains if there is no City to report, thus
example:

, California, USA

I want the comma to go away, if no city
-----Original Message-----
Try:
=[LocationCity] & ", " + [LocationState] & ", " + [LocationCountry]

There is a subtle difference between the two concatenation operators:
"A" & Null results in "A"
"A" + Null results in: Null


I have a field on the report that includes City, State,
Country. It is written so that if there is no State, it
will skip the state and just report City and Country.
(and omit comma after state)

=[LocationCity] & IIf(IsNull ([LocationState]),"",", " &
[LocationState]) & ", " & [LocationCountry]


How do I write this so if there is NO CITY, it will omit
the comma after city and just report State and Country ??

Many thanks
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top