Auto update/alter text on form

[Alsail77]

I am very new to Access and am essentially teaching myself as I build this database, so please excuse me if I am not using the proper naming conventions.

My database is to track information based on properties and needs to be searchable by address. I have a form called "Main" with a text box from which the search is preformed with outputs to a continuous for called "PropQuery" which is populated by a Query of the same name. The user load/updates data from a form called "Entry" which contains a field "StreetName" which contains everything except the house number (West 23rd Street). The problem I have is that the user can enter West as "West", "W" or "W." and Street as "Street", "St" or "St." which can greatly affect the results of a search.

I want to make it so Access will, upon event click, check the "StreetName" field and alter any leading instances of East or West and convert the text to "E" or "W". And any trailing instances of Street to "St." I have a save/lock button where I plan to put the code but I can't figure out how to say iif(StreetName starts with "West " replace with "W "). I know how to accomplsih this in excel but am having trouble in Access



I know I will likely need to enter each possible conditon as its own iff statement and believe I will need about 10-12 to handel the variations so please let me know if there is something I am missing. I know dropdown boxes would work but prefer not to use them.

[June7]

This could be very tricky. If you only want to check the first 4 characters of the string, that could simplify. However, what about North and South? Or Northwest (check out addresses in Wash, DC such as Madison Dr NW).

Standardizing addresses is never easy - ordinals, abbreviations, misspellings (Fifth St or 5th Street) are all subject to personal preference and human error. So unless only one person does all the data entry and does it consistently, will always have complications. Having a strict set of rules for data entry and thoroughly educating staff might help but nothing is foolproof.

Replace function could find instance of West and change to W but consider:

West Westview Dr.

if not done right, could become WWview Dr.

Replace("West Westview Dr.", "West ", "W ")

would become W Westview Dr.

because of the space following West in the search parameter the second West will be ignored.