Searching for and writing data throughout the database

[MTarkington]

Good afternoon,

First, I'll apologize if this question has been covered. I've looked, but haven't found an answer that I thought would suit the problem.

I have an Access DB w/ 37000+ records. I need to perform the following on each of the records:

If Field1 like "*&2&*" then Field2 = "New Data"

I'm sure there's a way to automate this to bounce from record to record, but I haven't figured it out yet.

Any suggestions would be appreciated.

Thank you,
Mark

 

[sfreeman]

I am guessing, but I am assuming that what you really want is to test for any value of '2' within Field1, and if that is true, you want the value of Field2 to be 'New Data'.

I am guessing, because your criteria "*&2&*" will not get you that result. Anyway, the idea is still the same.

If my assumption is correct, then the SQL would be:

'************************************
UPDATE
tblField

SET
tblField.Field2 = "New Data"

WHERE
(((tblField.Field1) Like "*2*"));
'************************************


If you really want to search for the string '&2&' anywhre in Field1, then it would be nearly the same, but:

'************************************
UPDATE
tblField

SET
tblField.Field2 = "New Data"

WHERE
(((tblField.Field1) Like "*&2&*"));

'************************************

HTH

Sam_F
"90% of the problem is asking the right question.

 

[MTarkington]

Thank you for the response, and I understand exactly what to do, however, I'm not sure where I need to add this sql statement.

Should I put it under the command button, or can I just run it through the query window?

Thanks,
Mark

 

[pullingteeth]

You can run it through the query window if you like.

 

[sfreeman]

I agree, unlesss you need to do this more that once, just run an update query.

If you will need to do this more than once, then you could create a command button on a form to do it, but really this should be a part of your table or form design to minimize this type of maintenance.

Good luck....

Sam_F
"90% of the problem is asking the right question.