Update field question 1

[thrilliam]

I'm an access newb and need some help. I did some searching but couldn't find anything. This should be an easy one
I have just a simple table [City_State] [State]

Field [City_state] has information such as
Philadelphia, PA
Boston, MA
Houston, TX
Etc.

Field [State] is blank
how can calculate/ update [State] to just that states ,* from the [City_state]? Nothing i have tried has worked so far.
Thanks

 

[PHV]

A starting point (SQL code):
UPDATE yourTable
SET [State] = Right([City_state],2)
WHERE [City_state] Like '*, ??'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thrilliam]

Would I paste that into the SQL view of an update query?

 

[randy700]

Yes, paste PHV's code into your query and replace yourTable with your actual table name.


Randy

 

[thrilliam]

still doesn't seem to work. What am i doing wrong?

 

[randy700]

still doesn't seem to work. What am i doing wrong?
I don't know.... what have you done?


Randy

 

[thrilliam]

^ Good point haha

When I paste the code in and attempt to run it an input box appears asking me to input a value for the [State]field. What ever I put in there populates every record with that input value.

 

[MichaelintheUK]

There is no where condition in the SQL - e.g.

UPDATE Table3 SET Table3.state = Right([CityState],2)

thanks

Michael

 

[PHV]

an input box appears asking me to input a value for the [State]field
So, I rephrase my suggestion:
UPDATE NameOfYourTable
SET NameOfStateField = Right(NameCityfield,2)
WHERE NameCityfield Like '*, ??'

Replace the items in italic with the real names ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[thrilliam]

UPDATE NameOfYourTable
SET NameOfStateField = Right(NameCityfield,2)
WHERE NameCityfield Like '*, ??'


JACKPOT! Thank you greatly!

 

[thrilliam]

I just realized this post is probably in the wrong forum and would probably be best suited in Microsoft: Access Tables and Relationships Forum. Sorry for the mis post, as I did not initially see the other forums as options from the main page.

Thanks again for all the help.