String Parsing for multiple numbers

[kkulakow via AccessMonster.com]

Hello, I am having a heck of a time figuring this out and hope some of the
experts here can help me.
I have a table (FPTABLE) containing shipment data where the 'Reference' field
is used to store the reference numbers of the shipment. There can be one or
more reference numbers (a mix of letters and numbers) entered in this field,
usually separated by a space, a comma, or by nothing. There are no rules or
structure to how this information is entered. (I wish there were but we have
no control over it as the data is sent from another company).
I am trying to match the numbers in the 'Reference' field against the
'OrderNum' field in the ORDERSTABLE. My thought is to pull out only the
numbers from the 'Reference' field and place them in one or more columns. As
I can not find any standard separator, my thought is to pull only the numbers,
separating each contiguous number sequence with a comma. I can then parse
this column using the comma delimiter into two or more columns and match
these against the 'OrderNum' field which does have a structure and contains
only one order number per record. This is the only logical way I can see to
do this but please feel free to suggest better ways.

The following are some examples to show what I am trying to achieve.

FPTABLE.Reference Result
PO# NK10135352 SO#421861 10135352, 421861
PO# 900070887 & 90007093 900070887, 90007093
ln10141955/HX240557901 10141955, 240557901
NV:83742 PO:CI10142013 83742, 10142013


Can someone help me with the required code or better yet, a function I can
use in a query?

Thanks,
Kerry

 

[kingston via AccessMonster.com]

Public Function TestFunc(TextInput) As String

Dim strResult As String
Dim i As Integer

For i = 1 To Len(TextInput)
If IsNumeric(Mid(TextInput, i, 1)) Then
strResult = strResult & Mid(TextInput, i, 1)
ElseIf IsNumeric(Right(strResult, 1)) Then
strResult = strResult & ", "
End If
Next i

TestFunc = strResult

End Function

 

[kkulakow via AccessMonster.com]

This works perfectly! My only problem is I get an "Invalid use of Null" for
every record found by the query where the 'Reference' field is empty.
Probably some error trapping or something, but being a novice to programming,
I need some help on that as well. What I added to try to fix it is encased
in the 'Added New and 'End Added New.

Public Function TestFunc(TextInput) As String

Dim strResult As String
Dim i As Integer
'added new
On Error GoTo err_testfunc
'end added new

For i = 1 To Len(TextInput)
If IsNumeric(Mid(TextInput, i, 1)) Then
strResult = strResult & Mid(TextInput, i, 1)
ElseIf IsNumeric(Right(strResult, 1)) Then
strResult = strResult & ", "
End If
Next i

TestFunc = strResult

'added new
EXIT_TestFunc:
Exit Function

err_testfunc:
MsgBox Err.Description
Resume EXIT_TestFunc
'end added new

End Function

Public Function TestFunc(TextInput) As String

Dim strResult As String
Dim i As Integer

For i = 1 To Len(TextInput)
If IsNumeric(Mid(TextInput, i, 1)) Then
strResult = strResult & Mid(TextInput, i, 1)
ElseIf IsNumeric(Right(strResult, 1)) Then
strResult = strResult & ", "
End If
Next i

TestFunc = strResult

End Function

Hello, I am having a heck of a time figuring this out and hope some of the
experts here can help me.

[quoted text clipped - 27 lines]

Thanks,
Kerry

 

[kingston via AccessMonster.com]

In your query, use the criteria Is Not Null or the function Nz(). This way a
Null will never be passed to the parsing function.

This works perfectly! My only problem is I get an "Invalid use of Null" for
every record found by the query where the 'Reference' field is empty.
Probably some error trapping or something, but being a novice to programming,
I need some help on that as well. What I added to try to fix it is encased
in the 'Added New and 'End Added New.

Public Function TestFunc(TextInput) As String

Dim strResult As String
Dim i As Integer
'added new
On Error GoTo err_testfunc
'end added new

For i = 1 To Len(TextInput)
If IsNumeric(Mid(TextInput, i, 1)) Then
strResult = strResult & Mid(TextInput, i, 1)
ElseIf IsNumeric(Right(strResult, 1)) Then
strResult = strResult & ", "
End If
Next i

TestFunc = strResult

'added new
EXIT_TestFunc:
Exit Function

err_testfunc:
MsgBox Err.Description
Resume EXIT_TestFunc
'end added new

End Function

Public Function TestFunc(TextInput) As String

[quoted text clipped - 18 lines]

 

[kkulakow via AccessMonster.com]

Works like a charm! You are truly Fantastic! Thank you!

In your query, use the criteria Is Not Null or the function Nz(). This way a
Null will never be passed to the parsing function.

This works perfectly! My only problem is I get an "Invalid use of Null" for
every record found by the query where the 'Reference' field is empty.

[quoted text clipped - 36 lines]