Access Macro

Topic

New Reply

I have an Access Query which returns records for business units. I would like to have code that changes those business units of “1” to “0001”.

I’m not sure how to write the code and loop it for each record in the query.

Thanks for your assistance.

John

Reply | Quote

Replies

I found a solution where one can use Find & Replace (Ctrl-H).

I am still curious if it can be done by code.

John

Reply | Quote

As Wendell indicates, you can use an update query. This can be done from code:

Code:

Dim strSQL As String
strSQL = "UPDATE tblSomething SET BusinessUnit = '0001' WHERE BusinessUnit = '1'"
CurrentDb.Execute strSQL

This is far more efficient than looping through the records.

Reply | Quote

Yes, it can be done in code – it involves using DAO or ADO and looping through the recordset returned by the query and changing the values. Presumably the field where the data is being stored is a text field. If it is a numeric field, even though you put “0001” in the field, it is still stored as one. And there are several other ways to achive the result. One thing would be to apply a simple format to the field so that the query always formatted the data with leading zeros. You could also use an update query to update the field if it is a text field – for a large number of records that approach is preferrable.

Reply | Quote