Update Query to deal with "The"

  • Thread starter Steve Hart via AccessMonster.com
  • Start date
S

Steve Hart via AccessMonster.com

I've got a table with a field called Title. The data (30,000 records) has
been entered inconsistently. Some of the data is in the form "The Midnight
Prowler"; some is in the format "Midnight Prowler, The". I would like to
standardize on the latter format.

I'm looking to write an update query to standardize the data. I think I'm a
bit over my head. :)

Steve

ps Thanks to everybody on this board for help. You're a great resource!
 
M

Marshall Barton

Steve said:
I've got a table with a field called Title. The data (30,000 records) has
been entered inconsistently. Some of the data is in the form "The Midnight
Prowler"; some is in the format "Midnight Prowler, The". I would like to
standardize on the latter format.

I'm looking to write an update query to standardize the data.
Click to expand...


Be sure to backup your table before trying this kind of
thing:

UPDATE table
SET Title = Mid(Title,5) & ", The"
WHERE Left(Title, 4) = "The "
 
S

Steve Hart via AccessMonster.com

I found it myself and I'm so proud! (Well I searched this archive)

The solution I came up with is
update to: Mid(temp.title,5) & ", The"
criteria LIKE "The " & "*"

or (in SQL View)
UPDATE Temp SET Temp.Title = Mid(temp.title,5) & ", The"
WHERE (((Temp.Title) Like "The " & "*"));

Thanks for everything past and future.

Steve
 
S

Steve Hart via AccessMonster.com

Thanks Marshall. Look like our posts crossed in the mail. :)

Steve
 
Top