 |
28-Jul-2006, 08:25 AM
|  | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
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
"tom" wrote in message
news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>I need to strip out all the non numeric characters
> in a query. Please advise how to do this.
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
 |
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
WOW!!!!!
That is a real mouthfull.
What I really need is the following:
substring(string from pattern)
Extract substring matching POSIX regular expression
"Ken Snell (MVP)" wrote in message news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> Next lngLoop
> StripAllNonNumericChars = strTemp
> Exit Function
> End Function
>
>
> --
>
> Ken Snell
>
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
> >I need to strip out all the non numeric characters
> > in a query. Please advise how to do this.
> >
>
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
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.
--
Ken Snell
"Tom" wrote in message
news:5uGdnV9vrtjbzQbZnZ2dnUVZ_rWdnZ2d@comcast.com. ..
> WOW!!!!!
>
> That is a real mouthfull.
>
> What I really need is the following:
>
> substring(string from pattern)
>
> Extract substring matching POSIX regular expression
>
>
> "Ken Snell (MVP)" wrote in message
> news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
>> 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
>>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>>
>> "tom" wrote in message
>> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>> >I need to strip out all the non numeric characters
>> > in a query. Please advise how to do this.
>> >
>>
>>
>
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
'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) > Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
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
---------------------------
"tom" wrote in message
news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>I need to strip out all the non numeric characters
> in a query. Please advise how to do this.
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
The code wouldnt compile
"Graham R Seach" wrote in message
news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
> 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
> '-------------------------------------------------------------------------------
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> ' 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
> ---------------------------
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>>I need to strip out all the non numeric characters
>> in a query. Please advise how to do this.
>>
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
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 Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
up #error. Could that be the problem?
"Ken Snell (MVP)" wrote in message
news:ud2$KvklGHA.2128@TK2MSFTNGP04.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> 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
>
>
> "tom" wrote in message
> news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>>I need to strip out all the non numeric characters
>> in a query. Please advise how to do this.
>>
>
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
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 http://I.Am/DougSteele
(no e-mails, please!)
"tom" wrote in message
news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy .net...
> The code wouldnt compile
>
>
> "Graham R Seach" wrote in message
> news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
> > 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" wrote in message
> > news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
> >>I need to strip out all the non numeric characters
> >> in a query. Please advise how to do this.
> >>
> >
> >
>
>
|
28-Jul-2006, 08:25 AM
| | Guest | | | | | | | | | | Re: How to strip out non numeric characters in a query
The problem is with the enum. I am using
Access 97. I am worried about upgrading for Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
fear of breaking the code I have there.
Is there problems with upgrading from Access 97.
"Douglas J Steele" wrote in message
news:e9800OvlGHA.3576@TK2MSFTNGP04.phx.gbl...
> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "tom" wrote in message
> news:HaWmg.155894$F_3.34023@newssvr29.news.prodigy .net...
>> The code wouldnt compile
>>
>>
>> "Graham R Seach" wrote in message
>> news:upVZavqlGHA.3528@TK2MSFTNGP02.phx.gbl...
>> > 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
>> > ---------------------------
>> >
>> > "tom" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11936
>> > news:QvEmg.48978$fb2.45087@newssvr27.news.prodigy. net...
>> >>I need to strip out all the non numeric characters
>> >> in a query. Please advise how to do this.
>> >>
>> >
>> >
>>
>>
>
>
| 
Support Us! Become a Promoter! | | Gurfateh ji, you can become a SPN Promoter by Donating as little as $10 each month. With limited resources & high operational costs, your donations make it possible for us to deliver a quality website and spread the teachings of the Sri Guru Granth Sahib Ji, to serve & uplift humanity. Every contribution counts. Donate Generously. Gurfateh! |
 (View-All)
Members who have read this thread : 0
| | There are no names to display. |
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | | | | Tools | Search | | | | | Display Modes |  Linear Mode |
Posting Rules
| You may not post new threads You may not post replies You may not post attachments You may not edit your posts
HTML code is On
| | | |  | » Active Discussions | | | | | | | | | Panjabi Today 17:56 PM 12 Replies, 258 Views | | | | | | | | | | | | | | | | | | | | | | » Books You Should Read... | | | |
