Update Query to deal with "The"

  • Thread starter Thread starter Steve Hart via AccessMonster.com
  • Start date 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!
 
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 "
 
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
 
Thanks Marshall. Look like our posts crossed in the mail. :)

Steve
 
Steve said:
Thanks Marshall. Look like our posts crossed in the mail. :)
Click to expand...


Sure looks like it ;-)

It's still good that you can find answers in the archives.
They are a tremendous resource.
 
Back
Top