Merge 2 fields in one table 2

[SarasotaIT]

I am trying to merge two fields into one field in the same table.

i.e.:
Field 1: City
Field 2: State

Desired result: a new field named City-State which contains the merged data in the format of "City, State".

Thanks in advance!!

ts

 

[THoey]

Why? You can always recalculate this on the fly.

But, if you must. Create your field in design view. Then run an UPDATE query:

UPDATE MyTable
SET [MyTable].[CityStateFieldName] = [MyTable].[CityFieldName] & [MyTable].[StateFieldName]; Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[SarasotaIT]

Terry:
Reason: My resulting database is limited to fewer fields than the original, therefore I must concatenate some fields to make the data fit the limited requirements. My new smaller database will be exported to CSV.
I tried the route you suggested, replacing your fieldnames with the correct ones, however, no results were returned. ???

Here is what I have in my update query (that is not working):
SET [TableWorking].[City-State] = [TableWorking].[City] & [TableWorking].[State]

Any suggestions? Thanks again!!
-the access newbie

 

[JoyInOK]

Before you do anything else, don't forget to add your space and comma in, or you'll end up with NashvilleTN and HoustonTX
SET [TableWorking].[City-State] = [TableWorking].[City] & ", " & [TableWorking].[State]

 

[JoyInOK]

Just to be clear, when you say you get "no results," do you mean when you switch to datasheet view from query design view? Because you normally see the current contents of the field you're trying to change, which in this case would be x number of blank fields (where x is the number of records you're about to change).
Or are you saying that when you run the query it notifies you that you're about to update no records?

 

[SarasotaIT]

JoyInOK & THoey:
Thanks much for your help! I was working in Design View, however, I entered SQL view and entered your code and *presto*! Go figure...anyway, it works! Thanks again!

ts

 

[THoey]

Thanks JoyinOK (where at? I was in OKC) for catching that I forgot the ", ". Rushed it...

Glad you got it working Sarasota... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[JoyInOK]

I'm OKC, too.

 

[SarasotaIT]

THoey:
Wow! This is wierd! I just relocated to Sarasota, FL from the Muskogee, OK area (between Okmulgee and Muskogee)! Hmmmmm... Strange!

-Terry

 

[JoyInOK]

'Tis strange! I've been in threads with folks from all over the US and a few other countries, and can't say I've ever run into anyone else from Oklahoma (course, I don't usually ask). Good thing I'm not subject to conspiracy theories!
Glad you got your problem solved. Hey, don't forget to give THoey a star for the help, and to mark this problem as solved.