input mask

[Guest]

Hi guys,
I need to strip off input mask from phone numbers. This is a mask that we
are currently using !(999) 000-0000;0;_ and I need to be able just to export
numbers only. I need to have input mask in table since I'm merging data from
Access into Word and if you don't have input mask numbers look like this
3331112222 in word document. Right now I'm using query to send that same data
to AS400 via ODBC linked table which works just fine except for date and
phone number (since both have input mask), I was able to fix date using this
statement in query FilingDate: Format([FDate],"mm/dd/yy") since I need to
have it in mm/dd/yy format but the phone seems to be a bigger problem for me.
I just need to have numbers from phone number field. Thank you in advance

 

[MGFoster]

Hi guys,
I need to strip off input mask from phone numbers. This is a mask that we
are currently using !(999) 000-0000;0;_ and I need to be able just to export
numbers only. I need to have input mask in table since I'm merging data from
Access into Word and if you don't have input mask numbers look like this
3331112222 in word document. Right now I'm using query to send that same data
to AS400 via ODBC linked table which works just fine except for date and
phone number (since both have input mask), I was able to fix date using this
statement in query FilingDate: Format([FDate],"mm/dd/yy") since I need to
have it in mm/dd/yy format but the phone seems to be a bigger problem for me.
I just need to have numbers from phone number field. Thank you in advance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should have stored the Phone number w/o the formatting. The Input
Mask that would allow that would be:

!(999) 000-0000;;_

No "0" between the semicolons. You could have used a query to export
the data to the Word merge. In that query you'd format the phone number
to have the punctuation. Always best to store raw data and only format
when about to display or, if required, export - as you do for the
FilingDate. Dates are actually stored as Doubles. E.g.: Now() at the
moment would be stored as:

38475.5098726852

Only during the display does the date get formatted to something humanly
readable.

Enough preaching... Below is a function I use to get rid of
punctuation. You can use it in your export query around the Phone
number. E.g.:

SELECT NoPunctuation(PhoneNumber) As Phone, etc.

Function NoPunctuation(ByVal str As String) As String
' Purpose:
' Remove all punctuation, including spaces, from the string.
' In:
' str The string.
' Out:
' The string w/o punctuation
' Created:
' mgf 3May2005
' Modified:
'

Const PUNC = "~!@#$%^&*()_+`-=[]\{}|;'"":,./<>? "

Dim i As Integer

str = Trim$(str)

For i = 1 To Len(PUNC)
str = Replace(str, Mid$(PUNC, i, 1), "")
Next i

NoPunctuation = str

End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnfRRYechKqOuFEgEQKr5wCgr/moIbzg4F1uO25S533TTk4HmaMAn3wa
XUIq8ktr37fPu3H/fqWNPORV
=IL8+
-----END PGP SIGNATURE-----

 

[Guest]

Thanx, I will try it tommorow...let you know.

Hi guys,
I need to strip off input mask from phone numbers. This is a mask that we
are currently using !(999) 000-0000;0;_ and I need to be able just to export
numbers only. I need to have input mask in table since I'm merging data from
Access into Word and if you don't have input mask numbers look like this
3331112222 in word document. Right now I'm using query to send that same data
to AS400 via ODBC linked table which works just fine except for date and
phone number (since both have input mask), I was able to fix date using this
statement in query FilingDate: Format([FDate],"mm/dd/yy") since I need to
have it in mm/dd/yy format but the phone seems to be a bigger problem for me.
I just need to have numbers from phone number field. Thank you in advance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should have stored the Phone number w/o the formatting. The Input
Mask that would allow that would be:

!(999) 000-0000;;_

No "0" between the semicolons. You could have used a query to export
the data to the Word merge. In that query you'd format the phone number
to have the punctuation. Always best to store raw data and only format
when about to display or, if required, export - as you do for the
FilingDate. Dates are actually stored as Doubles. E.g.: Now() at the
moment would be stored as:

38475.5098726852

Only during the display does the date get formatted to something humanly
readable.

Enough preaching... Below is a function I use to get rid of
punctuation. You can use it in your export query around the Phone
number. E.g.:

SELECT NoPunctuation(PhoneNumber) As Phone, etc.

Function NoPunctuation(ByVal str As String) As String
' Purpose:
' Remove all punctuation, including spaces, from the string.
' In:
' str The string.
' Out:
' The string w/o punctuation
' Created:
' mgf 3May2005
' Modified:
'

Const PUNC = "~!@#$%^&*()_+`-=[]\{}|;'"":,./<>? "

Dim i As Integer

str = Trim$(str)

For i = 1 To Len(PUNC)
str = Replace(str, Mid$(PUNC, i, 1), "")
Next i

NoPunctuation = str

End Function
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnfRRYechKqOuFEgEQKr5wCgr/moIbzg4F1uO25S533TTk4HmaMAn3wa
XUIq8ktr37fPu3H/fqWNPORV
=IL8+
-----END PGP SIGNATURE-----

 

[Guest]

I have tried your suggestion and it's not working, I'm probably screwing it
up somehow. Here is what I have done; I created Module (NoPunctuation) with
this code:
Function NoPunctuation(ByVal str As String) As String
' Purpose:
' Remove all punctuation, including spaces, from the string.
' In:
' str The string.
' Out:
' The string w/o punctuation
' Created:
' mgf 3May2005
' Modified:
'

Const PUNC = "~!@#$%^&*()_+`-=[]\{}|;'"":,./<>? "

Dim i As Integer

str = Trim$(str)

For i = 1 To Len(PUNC)
str = Replace(str, Mid$(PUNC, i, 1), "")
Next i

NoPunctuation = str

End Function
but it will not compile (getting stuck on Replace) saying "Sub or Function
not defined", I need some heelp with that one. Also on query part I'm saying
this in field: MyPhone: (Select NoPuctuation([RespHomePhone])
or any other variant of this procedure will not work.... If you can give me
more details that would be great.

 

[MGFoster]

I have tried your suggestion and it's not working, I'm probably screwing it
up somehow. Here is what I have done; I created Module (NoPunctuation) with
this code:

but it will not compile (getting stuck on Replace) saying "Sub or Function
not defined", I need some heelp with that one. Also on query part I'm saying
this in field: MyPhone: (Select NoPuctuation([RespHomePhone])
or any other variant of this procedure will not work.... If you can give me
more details that would be great.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Replace() is a function introduced in Access 2000. What version do you
have? Below is a VBA version of the Replace function (provided by MS),
just substitute Replace(str, Mid$(PUNC, i, 1), "") with

ReplaceStr(str, Mid$(PUNC, i, 1), "", 0)

Put the ReplaceStr() function in a standard module.

In the query grid Field cell put this:

MyPhone: NoPunctuation([RespHomePhone])

The SELECT is what goes in the SQL view. After you've set up the Design
grid, switch views to the SQL view to see what the SQL statement looks
like. The SQL statement is what most people on this newsgroup post -
its easier to read than a description of each cell in the design grid.

== begin code ==
Function ReplaceStr(ByVal strText As String, ByVal strSearch As _
String, ByVal strReplace As String, Optional _
ByVal intCompMode As Integer) As String
'
' From MS Neat Code 97.

' Replaces the strSearch string with strReplace string in the strText
' string.
' Uses intCompMode to determine comparison mode
'
' Modified:
' mgf 27mar2001 Put Hungarian prefixes on variables.
' Removed the Replace = "" in If..Then
'

Dim strWork As String, intP As Integer

If Len(strText) > 0 Then
strWork = strText
intP = InStr(1, strWork, strSearch, intCompMode)
Do While intP > 0
strWork = Left$(strWork, intP - 1) & strReplace & _
Mid$(strWork, intP + Len(strSearch))
intP = InStr(intP + Len(strReplace) + 1, strWork, _
strSearch, intCompMode)
Loop
End If

ReplaceStr = strWork

End Function
== end code ==
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnk0b4echKqOuFEgEQKQKwCgmE+uASM6q1xQqQ/Y4CXCGhybl6MAoJmO
934v6Aw+TOLvS4/Sux3V7OPp
=j8Us
-----END PGP SIGNATURE-----

 

[Guest]

Thank You. That works like a charm. Currently I'm using Access97 but we are
moving to 2000 fairly soon. Take care..

I have tried your suggestion and it's not working, I'm probably screwing it
up somehow. Here is what I have done; I created Module (NoPunctuation) with
this code:

but it will not compile (getting stuck on Replace) saying "Sub or Function
not defined", I need some heelp with that one. Also on query part I'm saying
this in field: MyPhone: (Select NoPuctuation([RespHomePhone])
or any other variant of this procedure will not work.... If you can give me
more details that would be great.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Replace() is a function introduced in Access 2000. What version do you
have? Below is a VBA version of the Replace function (provided by MS),
just substitute Replace(str, Mid$(PUNC, i, 1), "") with

ReplaceStr(str, Mid$(PUNC, i, 1), "", 0)

Put the ReplaceStr() function in a standard module.

In the query grid Field cell put this:

MyPhone: NoPunctuation([RespHomePhone])

The SELECT is what goes in the SQL view. After you've set up the Design
grid, switch views to the SQL view to see what the SQL statement looks
like. The SQL statement is what most people on this newsgroup post -
its easier to read than a description of each cell in the design grid.

== begin code ==
Function ReplaceStr(ByVal strText As String, ByVal strSearch As _
String, ByVal strReplace As String, Optional _
ByVal intCompMode As Integer) As String
'
' From MS Neat Code 97.

' Replaces the strSearch string with strReplace string in the strText
' string.
' Uses intCompMode to determine comparison mode
'
' Modified:
' mgf 27mar2001 Put Hungarian prefixes on variables.
' Removed the Replace = "" in If..Then
'

Dim strWork As String, intP As Integer

If Len(strText) > 0 Then
strWork = strText
intP = InStr(1, strWork, strSearch, intCompMode)
Do While intP > 0
strWork = Left$(strWork, intP - 1) & strReplace & _
Mid$(strWork, intP + Len(strSearch))
intP = InStr(intP + Len(strReplace) + 1, strWork, _
strSearch, intCompMode)
Loop
End If

ReplaceStr = strWork

End Function
== end code ==
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnk0b4echKqOuFEgEQKQKwCgmE+uASM6q1xQqQ/Y4CXCGhybl6MAoJmO
934v6Aw+TOLvS4/Sux3V7OPp
=j8Us
-----END PGP SIGNATURE-----