Query to add text to data field 1

[jonkovach]

I have a number field, and I just want to run an update query to add numbers to that field... i.e. it's 8 now, I want it to be 3, 5, 8. I can't figure out how to do that?
Any help is appreciated!
Thanks,
Jon

 

[Remou]

Please post what you have tried.

I am always a little suspicious of fields with lists. Are you confident of your database design?

 

[jonkovach]

Well, I am a novice at Access. But I've tried to put in [mailid] & "3".... and the like. That's all I can think of.
I am pretty sure the database is set up correctly - I had someone else set it up. It's a very, very basic database.
What it is, is just an organizational tool for my mailing lists. I work in marketing, and I use Access to store names, addresses, emails, etc. etc.... and then I also wanted just a very basic tracking system so I could tell what mailing I sent to particular people. I just have my mailings numbered - as that is how it was set up for me.
Hope that helps.
Jon

 

[Remou]

Ok, back-up first, then try something like:

strSQL="UPDATE tblTable SET ThisField = '3,5,' & ThisField"
CurrentDB.RunSQL strSQL

You can also have a Where statement:

strSQL="UPDATE tblTable SET ThisField = '3,5,' & ThisField " _
& "WHERE ThisField='8'"
CurrentDB.RunSQL strSQL

The above assumes that ThisField, the field you wish to update, is a text field.

 

[jonkovach]

Where do I put that in? In the "Update to:" field?
I tried that, and couldn't get it to work. Do I need to replace "ThisField" with the Field name in my database? I tried that, too, and didn't get any results.
I'm sorry I'm pretty slow with this.

 

[Remou]

Ok. Try this then. Open the the query design screen and switch to SQL view, then paste this into the screen:

[tt]UPDATE tblTable SET ThisField = '3,5,' & ThisField[/tt]

Change tblTable to the name of your table and ThisField to the name of the field you want to update. Switch back to Design View and see it it looks OK. If it does, choose Run or click the exclamation mark on the toolbar.

*** WARNING ***
All records will be updated and you will not be able to undo the update.

 

[jonkovach]

Got it to work!!!
However, one last glitch.... it doesn't put commas, just the numbers.... so instead of 3,5,7.... it's 357

 

[Remou]

Did you include the commas, as in my example - '3,5,' ?

 

[jonkovach]

Yes, I did.

 

[Remou]

Please post the line you used, just cut and paste from SQL view.


Welcome to Tek-Tips , please make sure you read the FAQs, especially faq181-2886.

 

[jonkovach]

UPDATE tblContacts SET mail1 = '4,5,6,' & mail1

 

[Remou]

It seems that mail1 is a number field, not a text field. You need a text field to do what you want.

 

[jonkovach]

Changed it to text, and it works! Thanks!!!!!!!!!!