Results 1 to 4 of 4

Thread: Access Macro

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found a solution where one can use Find & Replace (Ctrl-H).

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

    John

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •