Lookup a "Closest Match" value in a table

[JB]

Hello,

I have a 2 column table called tblstorecat. It has a text field and a
corresponding number field: storename & storenumber

I have a data entry form that has a text field called title. When a user
types in the title of the product, I would like to populate another field, in
the same table as title, called newstorenumber.

So I need either a query or event code to take the text from title and
compare it to storename, retrieve the corresponding storenumber, and save
that number into newstorenumber.

Finally, storename will contain strings like "pepsi" or "coke" but title
will contain strings like "pepsi can" or "coke bottle" so I need a way for
access to give me the closest match.

So user types "pepsi can" and suddenly newstorenumber = 1 (for pepsi)

It doesn't need to be visible to the user, so a query expression would be
fine.

Thank you very much in advance

 

[Ed Metcalfe]

Hello,

I have a 2 column table called tblstorecat. It has a text field and a
corresponding number field: storename & storenumber

I have a data entry form that has a text field called title. When a user
types in the title of the product, I would like to populate another field,
in
the same table as title, called newstorenumber.

So I need either a query or event code to take the text from title and
compare it to storename, retrieve the corresponding storenumber, and save
that number into newstorenumber.

Finally, storename will contain strings like "pepsi" or "coke" but title
will contain strings like "pepsi can" or "coke bottle" so I need a way for
access to give me the closest match.

So user types "pepsi can" and suddenly newstorenumber = 1 (for pepsi)

It doesn't need to be visible to the user, so a query expression would be
fine.

Thank you very much in advance

In the example you gave you can just use a wildcard criteria in your SQL
statement to return a recordset of matching values:

SELECT [NewsStoreNumber] FROM tblStoreCat WHERE [StoreName} Like
"*TextBoxName*";

This may of course return multiple records, or no records at all (depending
on what the user types).

You can then use the following code (or something like it) to populate your
second textbox. This code will need to run from the AfterUpdate event of
your first textbox:

Dim ThisDB as DAO.Database
Dim rstNewsStoreNos as DAO.Recordset

Set ThisDB=CurrentDB()
Set rstNewsStoreNos=ThisDB.OpenRecordset("SELECT [NewsStoreNumber] FROM
tblStoreCat WHERE [StoreName} Like "*TextBoxName*";",dbOpenSnapshot)

Me.txtNewsStoreNumber= rstNewsStoreNos!NewsStoreNumber

rstNewsStoreNos.Close
ThisDB.Close
Set rstNewsStoreNos=Nothing
Set ThisDB=Nothing

Note that this example does not take into account the situations where more
than one record, or no records, are returned. The code isn't tested
either...

Ed Metcalfe.