Crosstab header with a period

[Randy Harris]

I have a report based on a Crosstab query. The crosstab puts city names as
columns. This worked fine until the users added St. Louis. In the query,
Jet substitutes St_ Louis, but the report bombs, with a message that Jet
couldn't find a field named St.[ Louis]. Anyone have a suggestion how to
deal with this?

[Salad]

Randy Harris wrote:

I have a report based on a Crosstab query. The crosstab puts city names as
columns. This worked fine until the users added St. Louis. In the query,
Jet substitutes St_ Louis, but the report bombs, with a message that Jet
couldn't find a field named St.[ Louis]. Anyone have a suggestion how to
deal with this?

Maybe in the query change something to
CityName:IIF(Instr([City],".") = 0,[City],
Left([City],Instr([City],".") -1 &
Mid([City],Instr([City],".") +1)

Basically, the above remioves the period from the city if a period exists in
the name. You could also pass the city name to a function
CityName : CleanUpCity([City])
and create a function that removes anything that isn't an alpha character so
you can remove dashes and periods and numbers and apostrophes.

Function CleanUpCity(varCity As Variant) As Variant
If Not IsNull(varCity) Then
Dim strHold As String
Dim intFor As Integer
For intFor = 1 to len(varCity)
If (Ucase(Mid(varCity,intFor,1) >= "A" and _
Ucase(Mid(varCity,intFor,1) <= "Z") Or
Mid(varCity,intFor,1) = " " then
CleanUpCity = CleanUpCity & Mid(varCity,intFor,1)
endif
next
endif
endfunction

This removes anything not an alpha or space. You would need to change
references to City to CityName.