Help - how could I query a memo field and get one or more 4-character data ? Tough as hell for us to

  • Thread starter Thread starter TIA TIA TIA - Jim
  • Start date Start date
T

TIA TIA TIA - Jim

The field was bastardized ... It is a memo field that was designed to
capture certain narrative, instead it has been used (and by necessity)
and continues to be used to capture employee numbers ... Sometimes it
has only one employee number and sometimes it has several (as many as
20 sometimes) ...

The data entry folks separate the employee numbers, SOMETIMES, by
using a space, SOMETIMES using a comma or semi-colon and sometime
using single or double quotes in front of and at end of the 4
characters ... the four characters ranges from 0001 thru 9999 ...

Here's a good example of the data as entered in one memo field ...

1234, 2345; "3456" '4567' (5678) ...

Kindly help, how do I create a query (hopefully in design view) that
would produce, in this example, the following unique records ...

1234
2345
3456
4567
5678

TIA
jim
 
The field was bastardized ... It is a memo field that was designed to
capture certain narrative, instead it has been used (and by necessity)
and continues to be used to capture employee numbers ... Sometimes it
has only one employee number and sometimes it has several (as many as
20 sometimes) ...

The data entry folks separate the employee numbers, SOMETIMES, by
using a space, SOMETIMES using a comma or semi-colon and sometime
using single or double quotes in front of and at end of the 4
characters ... the four characters ranges from 0001 thru 9999 ...

Here's a good example of the data as entered in one memo field ...

1234, 2345; "3456" '4567' (5678) ...

Kindly help, how do I create a query (hopefully in design view) that
would produce, in this example, the following unique records ...

1234
2345
3456
4567
5678

TIA
jim
Click to expand...

That's a real bear for a Query. I think you would need to either use "Regular
Expressions" (do a Bing or Google search for the term) or write some custom
VBA code to parse through the memo text looking for - I presume - just
four-digit substrings. These would need to be written to a table using
recordset operations. If the memo field contains phone numbers like
232-555-1212 you will probably incorrectly pull the 1212, but I know of no
good way to avoid that.

Good luck... and please swat whoever implemented this mess and permitted it to
continue for so long with a wet noodle!
 
Many thanks John ... I'll google Reg Exp and test if I could get some
progress ... don't know vba, have done great things on access design,
so I'll see if I can get anywhere on this ...

MUCH APPRECIATED
jim
 
Jim,

Is there anything else in this field, besides the above set of numbers?

Use the function below to interpret the text, clean up the punctuation
marks, and parse the text into it's various parts. This function will accept
a variant, which allows for a NULL value being passed, and a numeric value to
indicate which position you want to retrieve.

Public Function fnParseText(ByVal SomeValue As Variant, Position As Integer)
As Variant

Dim aArray() As String

'If the value passed was NULL, return a NULL
If IsNull(SomeValue) Then
fnParseText = Null
Exit Function
End If

'Get rid of all the punction from the memo field
SomeValue = Replace(SomeValue, ", ", " ")
SomeValue = Replace(SomeValue, ";", " ")
SomeValue = Replace(SomeValue, Chr$(34), " ")
SomeValue = Replace(SomeValue, Chr$(39), " ")
SomeValue = Replace(SomeValue, "(", " ")
SomeValue = Replace(SomeValue, ")", " ")

Do
SomeValue = Replace(SomeValue, " ", " ")
Loop Until InStr(SomeValue, " ") = 0
SomeValue = Trim(SomeValue)

Position = Position - 1
aArray = Split(SomeValue, " ")

If Position < LBound(aArray) Or Position > UBound(aArray) Then
fnParseText = Null
Else
fnParseText = aArray(Position)
End If

End Function

Then, create a table (tbl_Numbers) that contains a single field (intNumber)
with values ranging from 1 to 20 (or maybe 30, just in case).

Finally, create a query that looks something like:

SELECT yourTable.ID, tbl_Numbers.intNumber,
fnParseText(yourTable.MemoField, tbl_Numbers.intNumber) as Expr1
FROM yourTable, intNumber
WHERE fnParseText(yourTable.MemoField, tbl_Numbers.intNumber) IS NOT NULL

Depending on the size of your recordset, this could take a while.
 
Back
Top