Lawrence

[ldodge]

Hi all-
I have a field (text) that currently has between a 4 and 6 digit number in it. I need to add the letter "R" to all entries in the field. Any thoughts on how to do this. (Once it is done, I have a validation rule to ensure new entries get the "R")

[Bob Fitz]

Create an Update Query.

Post back if you need more help.

[ldodge]

Before I really screw it up and loose all the existing #'s, please give me a detailed code to do this. Thanks
LCD

[nicknameoscar]

Before you screw up and loose[sic] all the existing #'s - make a backup copy of your database and experiment with one or the other.

[Bob Fitz]

Can you tell us the name of the field to be changed and the name of the table that holds the field.

[ldodge]

SpecSheets is the table
Product# is the field

[Bob Fitz]

Hi

First and most important. MAKE A BACK UP !


If the field is actually called Product# with the # sign
use this

UPDATE SpecSheets SET SpecSheets.[Product#] = "R" & [Product#];

If the field is actually called Product# without the # sign
use this

UPDATE SpecSheets SET SpecSheets.Product = "R" & [Product];

Cut and paste the require code into the SQL of a new query.
Then click the "Run" icon on the main toolbar (It looks like an exclamation mark ! ) .
Only do this once. Each time the query is run the "R" will be added to all existing records.

Job done.

[ldodge]

What did I do wrong, I went to the update query screen and entered in the code you gave me in the "Update To:" line (see attached), then "run query" and nothing happened.

[Bob Fitz]

When you say you "ran" the query, what exactly did you do?

If you clicked the icon on the far left of the toolbar then this is incorrect.
You need to click on the "Run" icon on the main toolbar (It looks like an exclamation mark ! )
You will know you have the correct icon if you hold the mouse pointer over it because the word "Run" will popup.

[ldodge]

In the Disign tab in results group, i clicked on the red !

[Bob Fitz]

Sorry, I've only just noticed that you are using A2010. I got a bit mixed up because I've also been helping someone using A2003.

I haven't used A2010 so the toolbar placings etc may well be different.

Have you managed to run the update query now? or do you need some help from someone more familiar with A2010?

If you've managed to run it, did it work?

[ldodge]

No it still doesn't work. Oh well thanks for your help
LCD

[Bob Fitz]

You could also paste this proc into a General module

 
Public Sub UpdateMyTable()
On Error GoTo Err_UpdateMyTable_Error
    DoCmd.RunSQL "UPDATE SpecSheets SET SpecSheets.[Product#] ='R' & [Product#];"
Exit_ErrorHandler:
    Exit Sub
Err_UpdateMyTable_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
    Resume Exit_ErrorHandler
End Sub

and run it to do the same job.

[nicknameoscar]

I don't have Access 2010 either but can you post the SQL statement for the query that you posted an image of a few posts up? The image is not totally clear (even after I click on it) but it looks like there is a - (dash) to the left of the "R-" (or is that an = ). I don't think I have used an = in front of an Update expression before.

What error messages do you get or does it say it is updating X number of records but no records are updating.

Also, from what I have read it is not a good idea to use symbols (such as #) in table field names.

[nicknameoscar]

Just saw a post by one of the experts here (sorry, forgot the name of that expert already - old age is for the birds) that said you have to supply a criteria for an Update query (I did know that but totally forgot). So, put a Like * or Not Is Null statement in the criteria field.