Mar 31 2009, 08:18 AM
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?
Mar 31 2009, 08:24 AM
1) You may find it easier to implement the street in each textbox:
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.
Mar 31 2009, 08:28 AM
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?
Mar 31 2009, 08:43 AM
You would probably want a custom function...
Public Function FullSuffixName(sInput As String) As String
FullSuffixName = DLookup("FullName" "SuffixTable", "Suffix = " & sInput)
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:
UPDATE MyTable SET StreetSuffix = (SELECT FullName FROM SuffixTable WHERE Suffix = StreetSuffix)
Mar 31 2009, 08:44 AM
You would use a table that contained the conversion data,
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here