Update Query to deal with "The"

[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!

 

[Marshall Barton]

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.

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 "

 

[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

 

[Steve Hart via AccessMonster.com]

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

Steve

 

[Marshall Barton]

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

Sure looks like it ;-)

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