Is there a way to replace the first 5 numbers in each cell with 9999?

[tmg73]

Hi there,
Is there a macro or other shortcut to replace the first 5 numbers in a whole column of numbers with 99999? I've been doing this manually and it's a royal pain.



Thanks!
--Todd

[Bob Fitz]

Run an "Update" query. Something like:

UPDATE YourTableName SET YourTableName.YourFieldName= "99999" & Mid("YourFieldName",5);

[John_G]

The expression needs one change :

UPDATE YourTableName SET YourTableName.YourFieldName= "99999" & Mid("YourFieldName",6);

Everything after column 5 starts in column 6.

John

[Bob Fitz]

The expression needs one change :

UPDATE YourTableName SET YourTableName.YourFieldName= "99999" & Mid("YourFieldName",6);

Everything after column 5 starts in column 6.

John

You are of course quite right about the "6", but I've just realized that the quote marks around the YourFieldName should actually be square brackets. So I think it should be:
UPDATE YourTableName SET YourTableName.YourFieldName= "99999" & Mid([YourFieldName],6);

[tmg73]

You are of course quite right about the "6", but I've just realized that the quote marks around the YourFieldName should actually be square brackets. So I think it should be:
UPDATE YourTableName SET YourTableName.YourFieldName= "99999" & Mid([YourFieldName],6);

OK, so how do I do this for an entire column of data?

[Bob Fitz]

OK, so how do I do this for an entire column of data?

Not sure what you mean. That will change the value in every row, or do you mean how do you replace all the digits rather than just the first 5.

[tmg73]

Not sure what you mean. That will change the value in every row, or do you mean how do you replace all the digits rather than just the first 5.

No, just the first 5 digits.

I apologize but I am a total Access newbie. I am trying to use the query design. I don't see anywhere to add a whole line of code like you are instructing.

I have the query run to select the data, and now it is asking for "Update To:".

Don't worry, I am trying this on a copy so I don't mess up the original..

[tmg73]

OK, I figured out I have to place "99999" & Mid([YourFieldName],6) in the Update To: box. It appears to work. Can I get it to ignore cells with nothing in them?

[Bob Fitz]

No, just the first 5 digits.

I apologize but I am a total Access newbie. I am trying to use the query design. I don't see anywhere to add a whole line of code like you are instructing.

I have the query run to select the data, and now it is asking for "Update To:".

Don't worry, I am trying this on a copy so I don't mess up the original..

Said, we should have warned you about making a backup.
Anyway, copy the posted code. open a new query in Design View. If you get a Dialog box open asking you which tables to add to the query, just close it, without selecting any tables. Once it has closed you should see a button on the toolbar with SQL on it. Click that then paste the code into the open pane. Then click the Run button on the toolbar. It looks like an exclamation mark.

[tmg73]

Said, we should have warned you about making a backup.
Anyway, copy the posted code. open a new query in Design View. If you get a Dialog box open asking you which tables to add to the query, just close it, without selecting any tables. Once it has closed you should see a button on the toolbar with SQL on it. Click that then paste the code into the open pane. Then click the Run button on the toolbar. It looks like an exclamation mark.

Hi, sorry, re-posting so you see this:

OK, I figured out I have to place "99999" & Mid([YourFieldName],6) in the Update To: box. It appears to work. Can I get it to ignore cells with nothing in them?

[June7]

Filter the records to be updated:

WHERE [YourFieldName] Not Is Null

[Bob Fitz]

OK, I figured out I have to place "99999" & Mid([YourFieldName],6) in the Update To: box. It appears to work. Can I get it to ignore cells with nothing in them?

Yes. Will look at that shortly. Just having dinner.

[tmg73]

OK, the "Is Not Null" goes into the "Criteria" parameter. Done!
Thanks!!

[Bob Fitz]

ttng73

Did you see June's post. That would go on the end of the code I posted, or you could change the query to Design View and put
Not Is Null
in the "Criteria" row.

[tmg73]

I did see it, thanks all!