I have a text box with an address in it. ie. 20 NETHERFORD RD. I want to extract the RD and translate the text box into the long form .... 20 NETHERFORD ROAD

ie 190 Scott Dr = 190 Scott Drive etc

Any ideas on how to do this?

Few things...

1) You may find it easier to implement the street in each textbox:

StreetNumber
StreetName
StreetSuffix

You also could make the Suffix a combobox listing all choices so there's no Rd. or RD or R or RS, just Road.

This is especially important if you intend to query against this sort of information (e.g. you want to query for all people living on a certain street) as this will make querying much easier.

2) If you are stuck with the structure being as is for whatever reason (and you realize that it's going to be unnecessary work), you could write up a parser to examine the string if there is a suffix, look it up against a table and replace the string with that full word. This would probably be best via a VBA function in conjunction with a table containing all synonyms for street, road, avenue and so forth.

ok, i have tried that , I stripped the suffix off , how would i compare it in a text box? I have CR in a text box, how would i compare it or convert it?

You would probably want a custom function...



SuffixTable is the table you would create to list all possible suffix with their full name counterpart.

If you want to the whole shebang via a query:

You would use a table that contained the conversion data,

DR.....Drive
RD.....Road
ST.....Street
etc.

You'll also want to give the users a way to add others to the table as well.

Then use DLookup into this table to get the expanded value,

In a text box, the control source would be something like this:

txtStreetNumber & " " & txtStreetName & " " & DLookup("[FieldName]", "[TableName]", "[FieldName] = '" & Me.NameOfTextBox & "'")

Then you need to store this new value in the database and get rid of the old one. You could do this as an update query, then change the structure of the table and forms so that you won't have to do it again.