I seek it here, I seek it there. But mainly in this column.

July 11, 2006 2:15 PM

Calling MS Access wizards... A batch find and replace conundrum.

I would really appreciate a solution to this problem, I just can't phrase it right on google to get the correct answer:

Problem : Access database, with many columns. One column has a serial number, sequential incrementing by 1 each record.

I need to replace given numbers with a word, the same word every time.

So in the column there would be

100123
100124
100125
100126
..
100999

And I need to replace just {100124,100125} with "RECEIVED" or similar. I need to do this with hundreds of entries daily, and it needs to be saved to the table once finished, so a query isn't any use. Doing this every day is pure torture by boredom.

Useful answers will receive my undying adulation, plus three free covert assassinations!

posted by snailer to computers & internet (13 comments total)

If the column is defined as a number, you can't change the data to a character value.

If the column is not defined specifically as a number, you need to change your query to an update query from the query menu.

You want your sql to look like this:

set {column_name} = "RECEIVED";
posted by SuperSquirrel at 2:42 PM on July 11, 2006

Well, the *complete* sql would look like this:

update {table}
set {column_name} = "RECEIVED";
posted by SuperSquirrel at 2:44 PM on July 11, 2006

OK, upon further review...

You're only replacing specific rows, not each one in the table? If so, my answer is wrong, and I am hugely disappointed in myself, because I had 3 folks already targeted! Damn.

Please clarify (for me and the other slow kids) if you need to replace one column in *every* row in the table with "RECEIVED", or only *certain* rows.

If it's the latter, how are those rows determined? Does the info come from another file, query, table, ????
posted by SuperSquirrel at 2:49 PM on July 11, 2006

If I understand you right, an Update Query would be perfect for this. I'm not sure why you think it wouldn't be right. Am I missing something?
posted by jasper411 at 3:29 PM on July 11, 2006

Hi!

Thanks for the answers. If I explain what it is I have to do, it may become clearer:

Static Access DB, gets downloaded from site
So I know what orders to process, I find and replace the order code {100123,100124...} with RECEIVED.

I have the list of received orders in notepad (they come to me from email), copy one, paste in "Find And Replace" then replace it.

So

100459
100622
100999

were received today.

I have to "copy, paste, find, replace" three times in the above example. I replace the above numbers with "RECEIVED07-11-2006" then use that query in my production software, to send the customer their 'in car pizza grill / sliced marmalade set / gourmet rachet holder'

Except I have to do it a few hundred times, with some extra rage thrown in!

It can't be a query, the serial numbers have to be replaced with the "RECEIVED07-11-2006 string.

I will fix this with new software someday, but as you well know, it's a long way from conception to implementation!

Many thanks for your answers!
posted by snailer at 3:40 PM on July 11, 2006

and SuperSquirrel, I was hoping my lifelong adulation would be enough, but if your bloodlust must be satisfied, so be it...
posted by snailer at 3:41 PM on July 11, 2006

This isn't fully-thought-out yet, but here's the direction my brain is going:

1) Load the text file (one order code per line with no other data, right?) into a new table in the same database. Copy/paste works fine for this. We'll call the table [Imports].

2) Run a query like this one:

UPDATE [MainTable]

  SET [MainTable].OrderCode = "RECEIVED07-11-2006"

  WHERE [Imports].OrderCode = [MainTable].OrderCode

3) Empty out the [Imports] table.

If you want to see what records would be updated without making any changes, this query would return all the rows that would be affected:

SELECT OrderCode from [MainTable]

  WHERE [Imports].OrderCode = [MainTable].OrderCode

(To enter these in Access, make a new query, and then go to View -> SQL View.)
posted by CrayDrygu at 3:55 PM on July 11, 2006

Are you saying the reason a query can't be used is because you're trying to put a string value into a number field and you're getting a type conversion error?

If that's true, how are you putting the string in there now?
posted by jasper411 at 4:01 PM on July 11, 2006

Jasper,

The number is in there as a text value, I just 'find and replace' by hand, repeatedly. It doesn't do that type conversion thing.

I'm just looking for some way that I can copy the values from notepad, one value per line, and batch 'find and replace' all occurences of the values with "RECEIVEDMM-DD-YYYY".

Thanks!
posted by snailer at 4:05 PM on July 11, 2006

I see I'll have to satisfy my bloodlust elsewhere, some other way. For now. :-)

Good job, CrayDrygu.
posted by SuperSquirrel at 4:07 PM on July 11, 2006

Thank you CrayDrygu!

This does work, and will save me many, many hours of RSI related pain. You and all other posters share my lifelong adulation.

Your "covert kill" order from will be posted to you this week. If you don't use them in the time allotted, they're gone!
posted by snailer at 4:15 PM on July 11, 2006

Glad to be of help!

Oh, I wanted to pass this along too: It's entirely possible to modify that query so it simply looks up the current date and uses that, instead of having to change the date manually each time you run it.

I don't have Access on this machine, so I can't test this, but I think it would go like this:

...

  SET [MainTable].OrderCode = "RECEIVED" & FORMAT(DATE(), "mm-dd-yyyy")

...

posted by CrayDrygu at 4:43 PM on July 11, 2006

If you have the values as a list, you could modify the above to:
UPDATE [MainTable]

SET [MainTable].OrderCode = "RECEIVED07-11-2006"

WHERE [MainTable].OrderCode in ('01234', '12453', '56743')
posted by Four Flavors at 9:07 PM on July 12, 2006