Replacing special characters


G

Guest

I'm fairly new to Access and could use some guidance. I need to build a bulk
upload feed of my product catalog for Google Base. On the import to Google I
receive errors because the product description and titles for some products
contain special characters like trademark, copyright, degree, and other
symbols. I've written some queries to replace those characters with the
entity names but it's gotten way too complex. Here is the first query. I
started writing seperate queries because the nested iif's got to be too much
for me.

description:
IIf(InStr([product].[description],"®"),Replace(htmlencode([product].[description]),"®","&reg"),IIf(InStr([product].[description],"â„¢"),Replace(htmlencode([product].[description]),"â„¢","&trade"),IIf(InStr([product].[description],"â€"),Replace(htmlencode([product].[description]),"â€","&lquo"),[product].[description])))

Does anybody have any suggestions on how to simplify this? For some reason
the htmlencode statement doesn't convert all the characters. Is there another
form of that which will work on all special characters?

Thanks
 
Ad

Advertisements

G

Guest

Here is a function that strips out everything except letters, numbers, and
spaces. If you need to include or exclude other characters, determine the
characters ASCII value and modify the code as necessary.
To determine the code:
=Asc(TheCharacter)

Public Function StripString(strJunk As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String

lngLength = Len(strJunk)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strJunk, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Or intAscii = 32 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
StripString = strFixed
End Function

To use a User Defined Function in a query, it needs to be a Public function
in a Standard module. Then you create a calculated field in your query to
return the value:

SELECT StripString([product].[description]) As ProductDescr
 
G

Guest

Thanks Dave. I'll do some homework now to understand what you've sent me and
try it out.

Klatuu said:
Here is a function that strips out everything except letters, numbers, and
spaces. If you need to include or exclude other characters, determine the
characters ASCII value and modify the code as necessary.
To determine the code:
=Asc(TheCharacter)

Public Function StripString(strJunk As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String

lngLength = Len(strJunk)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strJunk, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Or intAscii = 32 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
StripString = strFixed
End Function

To use a User Defined Function in a query, it needs to be a Public function
in a Standard module. Then you create a calculated field in your query to
return the value:

SELECT StripString([product].[description]) As ProductDescr
--
Dave Hargis, Microsoft Access MVP


Rich B said:
I'm fairly new to Access and could use some guidance. I need to build a bulk
upload feed of my product catalog for Google Base. On the import to Google I
receive errors because the product description and titles for some products
contain special characters like trademark, copyright, degree, and other
symbols. I've written some queries to replace those characters with the
entity names but it's gotten way too complex. Here is the first query. I
started writing seperate queries because the nested iif's got to be too much
for me.

description:
IIf(InStr([product].[description],"®"),Replace(htmlencode([product].[description]),"®","®"),IIf(InStr([product].[description],"â„¢"),Replace(htmlencode([product].[description]),"â„¢","&trade"),IIf(InStr([product].[description],"â€"),Replace(htmlencode([product].[description]),"â€","&lquo"),[product].[description])))

Does anybody have any suggestions on how to simplify this? For some reason
the htmlencode statement doesn't convert all the characters. Is there another
form of that which will work on all special characters?

Thanks
 
G

Guest

As a breif description and to help you understand,

it looks at each character in the received string (strJunk) and converts it
into its ASCII value (see VBA Help for the Asc function).

It then uses this code:
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Or intAscii = 32 Then

To determine if it is a value you want to keep. If it is a value to keep,
it appends it to the output string (strFixed); otherwise, it bypasses it.
Simple as that.
As to the values it keeps:
48 - 57 are the digits 0 - 9
65 - 90 are upper case letters
97 - 122 are lower case letters
32 is a Space.
--
Dave Hargis, Microsoft Access MVP


Rich B said:
Thanks Dave. I'll do some homework now to understand what you've sent me and
try it out.

Klatuu said:
Here is a function that strips out everything except letters, numbers, and
spaces. If you need to include or exclude other characters, determine the
characters ASCII value and modify the code as necessary.
To determine the code:
=Asc(TheCharacter)

Public Function StripString(strJunk As String) As String
Dim lngCtr As Long
Dim lngLength As Long
Dim strTheCharacter As String
Dim intAscii As Integer
Dim strFixed As String

lngLength = Len(strJunk)
For lngCtr = 1 To lngLength
strTheCharacter = Mid(strJunk, lngCtr, 1)
intAscii = Asc(strTheCharacter)
If (intAscii >= 48 And intAscii <= 57) Or (intAscii >= 65 And
intAscii <= 90) _
Or (intAscii >= 97 And intAscii <= 122) Or intAscii = 32 Then
strFixed = strFixed & strTheCharacter
End If
Next lngCtr
StripString = strFixed
End Function

To use a User Defined Function in a query, it needs to be a Public function
in a Standard module. Then you create a calculated field in your query to
return the value:

SELECT StripString([product].[description]) As ProductDescr
--
Dave Hargis, Microsoft Access MVP


Rich B said:
I'm fairly new to Access and could use some guidance. I need to build a bulk
upload feed of my product catalog for Google Base. On the import to Google I
receive errors because the product description and titles for some products
contain special characters like trademark, copyright, degree, and other
symbols. I've written some queries to replace those characters with the
entity names but it's gotten way too complex. Here is the first query. I
started writing seperate queries because the nested iif's got to be too much
for me.

description:
IIf(InStr([product].[description],"®"),Replace(htmlencode([product].[description]),"®","®"),IIf(InStr([product].[description],"â„¢"),Replace(htmlencode([product].[description]),"â„¢","&trade"),IIf(InStr([product].[description],"â€"),Replace(htmlencode([product].[description]),"â€","&lquo"),[product].[description])))

Does anybody have any suggestions on how to simplify this? For some reason
the htmlencode statement doesn't convert all the characters. Is there another
form of that which will work on all special characters?

Thanks
 
J

Jesse

Dave - I don't know nothin' bout birthin' no babies (or coding
Access), but I understand logic and that is VERY nice. Simple,
elegant approach and as a bonus a clear description. (And, into my
files, 'cause some day I'll need it.) Thanks for the public service.
- Jesse
 
Ad

Advertisements

G

Guest

Glad I could help, Jesse. Thanks for the Kudos.
I have to keep it Simple, because I am :)
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top