Find and compare alpha-numeric convention

[Todd Waldron]

Hi all,

I have a unique problem (I think) in that I need to search a memo field for
a particular convention of Alpha/Numeric characters, extract the results to a
list, then compare the results list with another list for validation.

Basically I have a list of "valid" Alpha/Numeric codes in one field, then I
have a memo field in which end-users will type free-form text where they make
reference to the Alpha/Numeric codes from the field of "valid" codes. The
reason I need to validate the (user entered) Alpha/Numeric codes in the memo
field is that there are often mispellings. The codes in the memo field are
referenced in subsequent reports, thus they must be "valid" in order for the
information to be accurate/correct. It is very tedious to perform this
validation manually thus I was hoping to find a way to automate the process.

The Alpha/Numeric code conventions can be:

AAA### (3 alpha, 3 numeric, thus 6 characters total)

or

AAA#### (3 alpha, 4 numeric, thus 7 characters total)

All of the alpha/numeric characters in both conventions are variables, and
the position in which the codes appear with in the memo field also varies.
Thus the only constant is the character length of the codes and the
conventions themselves.

My thought is that I would have to write a function to evaluate the string
with in the memo field for the 'length' and 'convention' but not sure where
to start. Also, wondering if there is an easier way, or if this is even
possible.

Any help would be greatly appreciated.

Thanks,

Todd Waldron
Austin, Texas

 

[John Vinson]

My thought is that I would have to write a function to evaluate the string
with in the memo field for the 'length' and 'convention' but not sure where
to start. Also, wondering if there is an easier way, or if this is even
possible.

A Query using

Memofield LIKE "*[A-Z][A-Z][A-Z]###*"

will find occurances of a memofield containing this type of string. I
think you'll need some nontrivial VBA code to extract the code (or
codes) from within the string, though; InStr doesn't accept wildcards!

John W. Vinson[MVP]

 

[Ken Sheridan]

Todd:

Your problem here really stems from the fact that you are using a memo field
as a data structure. Using any field as a data structure is fundamentally at
variance with the principles of the relational model, and introduces these
sort of problems.

A more correct model would be to decompose the table so that the memo fields
are in a separate table along with a column for the alpha-numeric codes and a
foreign key column which references the primary key of your current table.
Enforcement of referential integrity will ensure that only valid codes can be
entered and this can be ensured at form level by using a combo box for users
to select the code. Data entry of the codes and memo field for each parent
record would be via a linked subform in the parent form.

Your new table would thus have one or more rows for each row in the current
table, one for each time a code appears in the current memo field, with the
memo field in each row containing one section of the text from the present
single field. If you wished you could still present the memo fields' data
and the codes in a report in the same way as at present by concatenating the
codes and fields from all the rows in the new table which reference the
report's current row from the existing table. This can easily be done with a
VBA function which iterates through the matching records and concatenates the
values. You might find, however, that presenting the codes and memo field
from the new table in a simple linked subreport in the detail section of the
parent report provides a more readable layout as the position of the codes in
relation to each section of text would be constant, e.g. you could have each
code at top left of each section of text it relates to.

Ken Sheridan
Stafford, England