Non-displayable characters

  • Thread starter Thread starter Clark Murray
  • Start date Start date
C

Clark Murray

I have a situation where I suspect, but haven't been able to prove, that some non-displayable characters in Access records are causing search and formatting problems. Is there a way to identify non-displayable characters in Access fields?
 
Clark,

Funny you should ask. I'd just posted some code which I think could help
here.

1. Add a reference to Microsoft VBScript Regular Expressions 5.5 (or the
latest version you have).

2. Add the following to a standard module:

Public Enum StripType
se_Char = &H1
se_Num = &H2
se_NonWord = &H4
se_Space = &H8
se_AllButChar = &H10
se_AllButNum = &H20
se_Custom = &H40
End Enum

Public Function StripEx(sText As String, lExpr As StripType, Optional
sUsrExpr As String = "") As String
'---------------------------------------------------------------------------
----
' Function to selectively strip any combination of the following from a
string:
' Characters (a-z or A-Z)
' Numbers (0-9)
' Non-Word characters (~!@#$%, etc. Anything other than a-z, A-Z, 0-9)
' Spaces
'---------------------------------------------------------------------------
----
'IMPORTANT: Requires a reference to Microsoft VBScript Regular Expressions
'---------------------------------------------------------------------------
----

Dim objRegEx As RegExp
Dim sRegExpr As String

Set objRegEx = New RegExp
If lExpr And se_Custom Then
'User-defined expression
sRegExpr = sUsrExpr
ElseIf lExpr And se_AllButChar Then
'Strip out everything except alpha characters
sRegExpr = "[^a-zA-Z]"
ElseIf lExpr And se_AllButNum Then
'Strip out everything except numbers
sRegExpr = "\D"
Else
'Custom strip
If lExpr And se_Char Then sRegExpr = "[a-zA-Z]"
If lExpr And se_Num Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\d"
If lExpr And se_NonWord Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\W"
Click to expand...
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >
0, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr

With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With

Set objRegEx = Nothing
End Function

3. Create the following query:
SELECT PK
FROM tblMyTable
WHERE Len([somefield] <> Len(StripEx(StripEx("abc123", 1), 2))

In the above SQL, replace "PK" with the name of the Primary Key in your
table. Also, replace "somefieldname" with the name of the field you are
checking. What this does, is to return the Primary Key of every record whose
field (called "somefield") contains characters other than numbers or alpha
characters. It does this by stripping out both numbers and characters (using
the regular expression service), then comparing the Len() function as it is
applied to the untreated field.

With what I've supplied, you'll have to run the query against every field in
every table, but with a bit of thought, I'm sure you can come up with
something that'll check every field in the table at the same time. That's my
way of saying I don't have time to do that right now. :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


I have a situation where I suspect, but haven't been able to prove, that
some non-displayable characters in Access records are causing search and
formatting problems. Is there a way to identify non-displayable characters
in Access fields?
 
Back
Top