Help with Query Please
in [Help]
|
Prev: Problems with JOINs
Next: Support contracts for my apps
From: John Spencer on 12 Jul 2010 09:41 All I have to add is BE CAREFUL. All the proposed solutions can give you bad results. 1234 Richard St could end up as 1234 RichaRoad Street 2679 Lasting Hollow Avenue could end up as 2679 LaStreeting Hollow Avenuenue. Also, the US Postal system actually wants Rd for Road and St for Street as well as Ct for Court, etc. At a minimum, I would use the table approach, but include spaces in the replace function to get better results. The inclusion of spaces (as below) should limit you to replacing only entire "words" - any part of the string surrounded by spaces. Trim(Replace(" " & [AddressField] & " ", " " & [OldValue] & " "," " & [New Value] & " ")) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Douglas J. Steele wrote: > What you're describing works well if you're trying to change the entire > value of the field. Unfortunately, since Kissi needs to change "1234 Main > St." to "1234 Main Street", you'd need to have something like: > > UPDATE tblChemData INNER JOIN tblRenameAnalyte > ON tblChemData.Analyte Like "*" & tblRenameAnalyte.Original & "*" > SET tblChemData.Analyte = > Replace(tblChemData.Analyte, tblRenameAnalyte.Original, > tblRenameAnalyte.Rename) > > (there's no reason for the WHERE clause in your SQL statement is wrong, btw, > since it's already in the ON clause) > > However, this approach is unable to handle situations like wanting to change > "1234 Main St, Apt 1A" to "1234 Main Street, Apartment 1A", should that be > an aim. >
From: zuckermanf on 13 Jul 2010 14:38 Please be careful, I got burned once using [Addr] = Replace([Addr], "St", "Street) [Addr] = Replace([Addr], "Rd", "Road") I ended up with some addresses that changed from "1234 First Ave" to "1234 FirStreet Ave" "1234 Rexford Ave" to "1234 RexfoRoad Ave" "1234 Rexford Street" to "1234 RexfoRoad Streetreet" :) Fred On Jul 12, 6:41 am, John Spencer <JSPEN...(a)Hilltop.umbc> wrote: > All I have to add is BE CAREFUL. > > All the proposed solutions can give you bad results. > 1234 Richard St could end up as 1234 RichaRoad Street > 2679 Lasting Hollow Avenue could end up as 2679 LaStreeting Hollow Avenuenue. > > Also, the US Postal system actually wants Rd for Road and St for Street as > well as Ct for Court, etc. > > At a minimum, I would use the table approach, but include spaces in the > replace function to get better results. The inclusion of spaces (as below) > should limit you to replacing only entire "words" - any part of the string > surrounded by spaces. > > Trim(Replace(" " & [AddressField] & " ", " " & [OldValue] & " "," " & [New > Value] & " ")) > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > > > Douglas J. Steele wrote: > > What you're describing works well if you're trying to change the entire > > value of the field. Unfortunately, since Kissi needs to change "1234 Main > > St." to "1234 Main Street", you'd need to have something like: > > > UPDATE tblChemData INNER JOIN tblRenameAnalyte > > ON tblChemData.Analyte Like "*" & tblRenameAnalyte.Original & "*" > > SET tblChemData.Analyte = > > Replace(tblChemData.Analyte, tblRenameAnalyte.Original, > > tblRenameAnalyte.Rename) > > > (there's no reason for the WHERE clause in your SQL statement is wrong, btw, > > since it's already in the ON clause) > > > However, this approach is unable to handle situations like wanting to change > > "1234 Main St, Apt 1A" to "1234 Main Street, Apartment 1A", should that be > > an aim.- Hide quoted text - > > - Show quoted text -
From: Bob Alston on 13 Jul 2010 14:44
zuckermanf(a)gmail.com wrote: > Please be careful, I got burned once using > [Addr] = Replace([Addr], "St", "Street) > [Addr] = Replace([Addr], "Rd", "Road") > > I ended up with some addresses that changed from > > "1234 First Ave" to "1234 FirStreet Ave" > "1234 Rexford Ave" to "1234 RexfoRoad Ave" > "1234 Rexford Street" to "1234 RexfoRoad Streetreet" > > :) > Fred > > > > > > On Jul 12, 6:41 am, John Spencer <JSPEN...(a)Hilltop.umbc> wrote: >> All I have to add is BE CAREFUL. >> >> All the proposed solutions can give you bad results. >> 1234 Richard St could end up as 1234 RichaRoad Street >> 2679 Lasting Hollow Avenue could end up as 2679 LaStreeting Hollow Avenuenue. >> >> Also, the US Postal system actually wants Rd for Road and St for Street as >> well as Ct for Court, etc. >> >> At a minimum, I would use the table approach, but include spaces in the >> replace function to get better results. The inclusion of spaces (as below) >> should limit you to replacing only entire "words" - any part of the string >> surrounded by spaces. >> >> Trim(Replace(" " & [AddressField] & " ", " " & [OldValue] & " "," " & [New >> Value] & " ")) >> >> John Spencer >> Access MVP 2002-2005, 2007-2010 >> The Hilltop Institute >> University of Maryland Baltimore County >> >> >> >> Douglas J. Steele wrote: >>> What you're describing works well if you're trying to change the entire >>> value of the field. Unfortunately, since Kissi needs to change "1234 Main >>> St." to "1234 Main Street", you'd need to have something like: >>> UPDATE tblChemData INNER JOIN tblRenameAnalyte >>> ON tblChemData.Analyte Like "*" & tblRenameAnalyte.Original & "*" >>> SET tblChemData.Analyte = >>> Replace(tblChemData.Analyte, tblRenameAnalyte.Original, >>> tblRenameAnalyte.Rename) >>> (there's no reason for the WHERE clause in your SQL statement is wrong, btw, >>> since it's already in the ON clause) >>> However, this approach is unable to handle situations like wanting to change >>> "1234 Main St, Apt 1A" to "1234 Main Street, Apartment 1A", should that be >>> an aim.- Hide quoted text - >> - Show quoted text - > What you are trying to do gets very complicated, as you are beginning to understand. Your best bet is to get software and access to the USPS database to validate addresses. YOU provide an address and if valid, it returns a standardized address. I saw such some time back but don't recall exactly where. I suggest some Google searching. Bob |