T
tom
I need to strip out all the non numeric characters
in a query. Please advise how to do this.
in a query. Please advise how to do this.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
Ken Snell \(MVP\)
Here is a function that will do this:
'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************
' ** This function strips all nonnumeric characters from a text string.
Function StripAllNonNumericChars(strOriginalString As String) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************
' ** This function strips all nonnumeric characters from a text string.
Function StripAllNonNumericChars(strOriginalString As String) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
T
Tom
WOW!!!!!
That is a real mouthfull.
What I really need is the following:
substring(string from pattern)
Extract substring matching POSIX regular expression
That is a real mouthfull.
What I really need is the following:
substring(string from pattern)
Extract substring matching POSIX regular expression
K
Ken Snell \(MVP\)
Unfortunately, I am not knowledgeable about REGEX and similar types of
pattern matching. John Nurick and Douglas Steele (both MVPs) are, however; I
will "ping" them to take a look at this thread.
pattern matching. John Nurick and Douglas Steele (both MVPs) are, however; I
will "ping" them to take a look at this thread.
G
Graham R Seach
Tom,
Firstly, 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
'-------------------------------------------------------------------------------
Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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, "|", "") & "\s"
End If
'Debug.Print "Regular Expression: " & sRegExpr
With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With
Set objRegEx = Nothing
End Function
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
Firstly, 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
'-------------------------------------------------------------------------------
Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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)
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr) >0, "|", "") & "\W"
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
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
T
tom
The code wouldnt compile
Graham R Seach said:Tom,
Firstly, 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
'-------------------------------------------------------------------------------
Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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)If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)0, "|", "") & "\W"End If0, "|", "") & "\s"
'Debug.Print "Regular Expression: " & sRegExpr
With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With
Set objRegEx = Nothing
End Function
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
tom said:I need to strip out all the non numeric characters
in a query. Please advise how to do this.
T
tom
Your code works but when I try to use it in a join
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?
D
Douglas J Steele
Did you correct for the word-wrap in Graham's code?
The 3 lines of code
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"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
The 3 lines of code
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"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
tom said:The code wouldnt compile
'---------------------------------------------------------------------------Graham R Seach said:Tom,
Firstly, 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
----Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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)
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
End If
'Debug.Print "Regular Expression: " & sRegExpr
With objRegEx
.Pattern = sRegExpr
.Global = True
StripEx = .Replace(sText, "")
End With
Set objRegEx = Nothing
End Function
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
T
tom
The problem is with the enum. I am using
Access 97. I am worried about upgrading for
fear of breaking the code I have there.
Is there problems with upgrading from Access 97.
Access 97. I am worried about upgrading for
fear of breaking the code I have there.
Is there problems with upgrading from Access 97.
Douglas J Steele said:Did you correct for the word-wrap in Graham's code?
The 3 lines of code
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"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
tom said:The code wouldnt compile
'---------------------------------------------------------------------------Graham R Seach said:Tom,
Firstly, 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
----Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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"
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
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
D
Douglas J Steele
Try:
Const se_Char As Long = &H1
Const se_Num As Long = &H2
Const se_NonWord As Long = &H4
Const se_Space As Long = &H8
Const se_AllButChar As Long = &H10
Const se_AllButNum As Long = &H20
Const se_Custom As Long = &H40
Public Function StripEx(sText As String, lExpr As Long, Optional sUsrExpr As
String = "") As String
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Const se_Char As Long = &H1
Const se_Num As Long = &H2
Const se_NonWord As Long = &H4
Const se_Space As Long = &H8
Const se_AllButChar As Long = &H10
Const se_AllButNum As Long = &H20
Const se_Custom As Long = &H40
Public Function StripEx(sText As String, lExpr As Long, Optional sUsrExpr As
String = "") As String
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
tom said:The problem is with the enum. I am using
Access 97. I am worried about upgrading for
fear of breaking the code I have there.
Is there problems with upgrading from Access 97.
Douglas J Steele said:Did you correct for the word-wrap in Graham's code?
The 3 lines of code
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"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
'---------------------------------------------------------------------------tom said:The code wouldnt compile
Tom,
Firstly, 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
----Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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"
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
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
K
Ken Snell \(MVP\)
?? Using the function in a join in a query? Post the SQL statement that you
created to do this so I can see what you mean.
created to do this so I can see what you mean.
T
Tom
SELECT
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1, invoice.[Invoice Number]
FROM invoice;
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1, invoice.[Invoice Number]
FROM invoice;
K
Ken Snell \(MVP\)
I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
data type? The function is returning a string data type, which I did so that
you are not limited in the length of the "numeric" string that you use.
Assuming that the number of digits in the purchase order number will fit
into a Long Integer value, change Query2 to this:
SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
--
Ken Snell
<MS ACCESS MVP>
data type? The function is returning a string data type, which I did so that
you are not limited in the length of the "numeric" string that you use.
Assuming that the number of digits in the purchase order number will fit
into a Long Integer value, change Query2 to this:
SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
--
Ken Snell
<MS ACCESS MVP>
Tom said:SELECT
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order
Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
Ken Snell (MVP) said:?? Using the function in a join in a query? Post the SQL statement that
you
created to do this so I can see what you mean.
T
Tom
purchase order.Purchase order number is a text type. Your query
is returning some values #error
I have not yet tried to strip out these and try the
query again. It seems that your code should
return a string, which I understand would be a
text type, so I am a bit confused.
is returning some values #error
I have not yet tried to strip out these and try the
query again. It seems that your code should
return a string, which I understand would be a
text type, so I am a bit confused.
Ken Snell (MVP) said:I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
data type? The function is returning a string data type, which I did so that
you are not limited in the length of the "numeric" string that you use.
Assuming that the number of digits in the purchase order number will fit
into a Long Integer value, change Query2 to this:
SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
--
Ken Snell
<MS ACCESS MVP>
Tom said:SELECT
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase Order
Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
Ken Snell (MVP) said:?? Using the function in a join in a query? Post the SQL statement that
you
created to do this so I can see what you mean.
--
Ken Snell
<MS ACCESS MVP>
Your code works but when I try to use it in a join
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?
Here is a function that will do this:
'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************
' ** This function strips all nonnumeric characters from a text
string.
Function StripAllNonNumericChars(strOriginalString As String) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
I need to strip out all the non numeric characters
in a query. Please advise how to do this.
K
Ken Snell \(MVP\)
Let's see some examples of data that are in "Source of Order" field .. by
any chance, can some of them be Null values? Assuming that they might be,
change the function to this so that it handles Null value being passed to
the function:
Function StripAllNonNumericChars(varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
any chance, can some of them be Null values? Assuming that they might be,
change the function to this so that it handles Null value being passed to
the function:
Function StripAllNonNumericChars(varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
Tom said:purchase order.Purchase order number is a text type. Your query
is returning some values #error
I have not yet tried to strip out these and try the
query again. It seems that your code should
return a string, which I understand would be a
text type, so I am a bit confused.
Ken Snell (MVP) said:I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
data type? The function is returning a string data type, which I did so
that
you are not limited in the length of the "numeric" string that you use.
Assuming that the number of digits in the purchase order number will fit
into a Long Integer value, change Query2 to this:
SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
--
Ken Snell
<MS ACCESS MVP>
Tom said:SELECT
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase
Order
Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
?? Using the function in a join in a query? Post the SQL statement
that
you
created to do this so I can see what you mean.
--
Ken Snell
<MS ACCESS MVP>
Your code works but when I try to use it in a join
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?
message
Here is a function that will do this:
'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************
' ** This function strips all nonnumeric characters from a text
string.
Function StripAllNonNumericChars(strOriginalString As String) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
I need to strip out all the non numeric characters
in a query. Please advise how to do this.
G
Graham R Seach
Ahhh, Access 97! Access 97 doesn't know about enums. Change the enums to
constants.
Public Const se_Char = &H1
Public Const se_Num = &H2
Public Const se_NonWord = &H4
Public Const se_Space = &H8
Public Const se_AllButChar = &H10
Public Const se_AllButNum = &H20
Public Const se_Custom = &H40
Public Function StripEx(sText As String, lExpr As Integer, Optional sUsrExpr
As String = "") As String
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
constants.
Public Const se_Char = &H1
Public Const se_Num = &H2
Public Const se_NonWord = &H4
Public Const se_Space = &H8
Public Const se_AllButChar = &H10
Public Const se_AllButNum = &H20
Public Const se_Custom = &H40
Public Function StripEx(sText As String, lExpr As Integer, Optional sUsrExpr
As String = "") As String
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
---------------------------
tom said:The problem is with the enum. I am using
Access 97. I am worried about upgrading for
fear of breaking the code I have there.
Is there problems with upgrading from Access 97.
Douglas J Steele said:Did you correct for the word-wrap in Graham's code?
The 3 lines of code
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"
If lExpr And se_Space Then sRegExpr = sRegExpr & IIf(Len(sRegExpr)
0, "|", "") & "\s"
should each be one line each. Try the following instead:
If lExpr And se_Num Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\d"
End If
If lExpr And se_NonWord Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\W"
End If
If lExpr And se_Space Then
sRegExpr = sRegExpr & _
IIf(Len(sRegExpr) > 0, "|", "") & "\s"
End If
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
tom said:The code wouldnt compile
Tom,
Firstly, 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
'---------------------------------------------------------------------------
----
Dim objRegEx As Object
Dim sRegExpr As String
Set objRegEx = CreateObject("VBScript.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"
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
You can then call this function from your query:
SELECT StripEx(myqueryfield, 32) As numbersonly
FROM tblMyTable
Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia
T
Tom
That code worked. I looked at your first code
and I think that should have worked as well.
Access should be using an empty string by default
IMO.
and I think that should have worked as well.
Access should be using an empty string by default
IMO.
Ken Snell (MVP) said:Let's see some examples of data that are in "Source of Order" field .. by
any chance, can some of them be Null values? Assuming that they might be,
change the function to this so that it handles Null value being passed to
the function:
Function StripAllNonNumericChars(varOriginalString As Variant) As String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
Dim strOriginalString As String
On Error Resume Next
strTemp = ""
strOriginalString = Nz(varOriginalString, "")
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
Tom said:purchase order.Purchase order number is a text type. Your query
is returning some values #error
I have not yet tried to strip out these and try the
query again. It seems that your code should
return a string, which I understand would be a
text type, so I am a bit confused.
Ken Snell (MVP) said:I'm guessing that [Purchase Order].[Purchase Order Number] is a numeric
data type? The function is returning a string data type, which I did so
that
you are not limited in the length of the "numeric" string that you use.
Assuming that the number of digits in the purchase order number will fit
into a Long Integer value, change Query2 to this:
SELECT CLng(StripAllNonNumericChars([Source of Order])) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
--
Ken Snell
<MS ACCESS MVP>
SELECT
FROM [Purchase Order] LEFT JOIN Query2 ON [Purchase Order].[Purchase
Order
Number] = Query2.Expr1;
Query2 is as follows:
SELECT StripAllNonNumericChars([Source of Order]) AS Expr1,
invoice.[Invoice Number]
FROM invoice;
?? Using the function in a join in a query? Post the SQL statement
that
you
created to do this so I can see what you mean.
--
Ken Snell
<MS ACCESS MVP>
Your code works but when I try to use it in a join
with a another table with text field, I am getting
a data type mismatch. Some of the entries are coming
up #error. Could that be the problem?
message
Here is a function that will do this:
'********************************
'* *
'* Fxn StripAllNonNumericChars *
'* *
'********************************
' ** This function strips all nonnumeric characters from a text
string.
Function StripAllNonNumericChars(strOriginalString As String) As
String
Dim blnStrip As Boolean
Dim intLoop As Integer
Dim lngLoop As Long
Dim strTemp As String, strChar As String
On Error Resume Next
For lngLoop = Len(strOriginalString) To 1 Step -1
blnStrip = True
strChar = Mid(strOriginalString, lngLoop, 1)
For intLoop = Asc("0") To Asc("9")
If strChar = Chr(intLoop) Then
blnStrip = False
Exit For
End If
Next intLoop
If blnStrip = False Then strTemp = strChar & strTemp
Next lngLoop
StripAllNonNumericChars = strTemp
Exit Function
End Function
--
Ken Snell
<MS ACCESS MVP>
I need to strip out all the non numeric characters
in a query. Please advise how to do this.
K
Ken Snell \(MVP\)
Tom said:That code worked. I looked at your first code
and I think that should have worked as well.
Access should be using an empty string by default
IMO.
Actually, there is a good reason for having Null and empty string as
potential values. Null means that there is no value for that field; empty
string means that the value is a blank (there is a value, but it contains no
characters). These are distinctly different values with good uses.
You can change the design of your database so that the default value of the
text field is an empty string and so that the field is required to have a
value. That will eliminate the possibility of Null values in the records in
the future. Before you do this, though, run an update query to change all
Null values in that field to an empty string.
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.