Module to cope with spaces and numbers

  • Thread starter Thread starter Wayne-I-M
  • Start date Start date
W

Wayne-I-M

Hi

I have a small module to get the numbers out of Post Codes (like zip codes)
which we use for an automated credit card payment system.

Public Function SplitPostCode(strInput As String) As String
Dim lngCounter As Long
Dim tmpChar As String
For lngCounter = 1 To Len(strInput)
tmpChar = Mid(strInput, lngCounter, 1)
If IsNumeric(tmpChar) Then
SplitPostCode = SplitPostCode & tmpChar
End If
Next lngCounter
End Function

To query
SELECT SplitPostCode([tblClients]![CDPostcode]) AS PCNum
FROM tblClients;


This works fine - But - the card companies are now insisting that (starting
in August) this year we send the numbers "as formated"

So if you have a postcode like this
WN4 7TH at the moment we send 47 but they want 4 7 (with a space)
WC1T3 8UF they want 13 8

UK post code are different length and so the space may by 3rd or 4th from
the left (or even - in the center of London - the 5th from the left). There
are always 3 digits after the space 1 number and 2 text.
 
Are you just looking to return the formatted number?

try:

Public Function PostNum(str As Variant) As Variant

Dim v As Variant
If IsNull(str) Then Exit Function

v = Split(str, " ")

PostNum = OnlyNumbers(v(0)) & " " & OnlyNumbers(v(1))

End Function

Public Function OnlyNumbers(myphone As Variant) As String

Dim i As Integer
Dim mych As String

OnlyNumbers = ""

If IsNull(myphone) = False Then

For i = 1 To Len(myphone)
mych = Mid$(myphone, i, 1)
If InStr("0123456789", mych) > 0 Then
OnlyNumbers = OnlyNumbers & mych
End If
Next i
End If


End Function

in the debug window:

? postnum("WC1T3 8UF")
13 8

? postnum("WN4 7TH")
4 7

? postnum("ABC1DEF2HIJ HELLO8")
12 8
 
This works fine - But - the card companies are now insisting that (starting
in August) this year we send the numbers "as formated"

So if you have a postcode like this
WN4 7TH at the moment we send 47 but they want 4 7 (with a space)
WC1T3 8UF they want 13 8
Click to expand...

DUH. That's DUMB. Bureaucrats...

You can make a tiny tweak to your existing code:

Public Function SplitPostCode(strInput As String) As String
Dim lngCounter As Long
Dim tmpChar As String
For lngCounter = 1 To Len(strInput)
tmpChar = Mid(strInput, lngCounter, 1)
If IsNumeric(tmpChar) Or tmpChar = " " Then
SplitPostCode = SplitPostCode & tmpChar
End If
Next lngCounter
End Function
 
Wayne-I-M said:
Hi

I have a small module to get the numbers out of Post Codes (like zip
codes)
which we use for an automated credit card payment system.

Public Function SplitPostCode(strInput As String) As String
Dim lngCounter As Long
Dim tmpChar As String
For lngCounter = 1 To Len(strInput)
tmpChar = Mid(strInput, lngCounter, 1)
If IsNumeric(tmpChar) Then
SplitPostCode = SplitPostCode & tmpChar
End If
Next lngCounter
End Function

To query
SELECT SplitPostCode([tblClients]![CDPostcode]) AS PCNum
FROM tblClients;


This works fine - But - the card companies are now insisting that
(starting
in August) this year we send the numbers "as formated"

So if you have a postcode like this
WN4 7TH at the moment we send 47 but they want 4 7 (with a space)
WC1T3 8UF they want 13 8

UK post code are different length and so the space may by 3rd or 4th from
the left (or even - in the center of London - the 5th from the left).
There
are always 3 digits after the space 1 number and 2 text.
Click to expand...
 
Back
Top