By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Crosstab header with a period

P: n/a
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?
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.