Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query Questions (97 SR2)

    Update Query-Changing part of field based on another field.

    Greetings,

    Searched on Update Query, but could not find the answer, so here I am <smile>. I am using Access 97.

    Question 1: I'm working on a fincance and accounting (F&A) database at work. In this instance, I'm interested in PARTIALLY changing the values of data in one field based on criteria from another field.

    I'm working on automatically correcting erroneous data. All fields are text fields. In this case, two fields are involved; EOE (Element of Expense) and APC (Account Processing Code). Both fields have a length of 4.

    I need an update query that will CHANGE the last two characters of the APC field to the text characters OA only if the EOE = 4140.

    EOE APC
    4140 ZADA
    4140 ZEDA
    4140 ZFEN
    4140 ZHDA
    4140 ZHDA
    4140 ZUDA
    4140 ZUDA
    4140 ZUDA
    4140 ZUDA
    4140 ZVDA
    4140 ZYDA
    4140 ZZDA

    In this example, the last two characters of the APC must be OA.


    Question 2: Working with the same table, but different fields.

    These F&A records have one line spread across several fields, and it's called the Total Card.
    Once the F&A records are cleaned up, they are uploaded into another database for further processing.
    The fields are called MANHRS (length 7) and BK4 (length 4). Both are text fields.

    In the below example, I need to delete the last 4 alphanumeric characters in the MANHRS field, leaving only
    the first 3 alphanumeric characters in place. In the BK4 field, I need to totally delete anything in that particular record. It appears that the BK4 field is totally null except for this Total Card line...hence my filter criteria in a query would be BK4 Is Not Null.

    EXAMPLE:
    Current Data:
    MANHRS BK4
    79C5570 K1

    Desired Result:
    MANHRS BK4
    79C

    I hope I've explained myself. I've tried using this in an Update query. Glad I backed up the table <smile>.
    UPDATE t_DIX_Bak SET t_DIX_Bak.APC = IIf("EOE"="4140","??OA","APC")
    WHERE (((t_DIX_Bak.EOE)="4140"));

    Regards,

    Bob in Indy

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Questions (97 SR2)

    You can update the records as you describe in your first question with the following Update query:
    <pre>UPDATE tblIFUpdate SET tblIFUpdate.APC = Left([APC],2) & "OA"
    WHERE (((tblIFUpdate.EOE)="4140"));
    </pre>


    Replace the all instances of tblIFUpdate with your tablename in the statement.
    Keep that backup and give it a try.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Questions (97 SR2)

    Thomas, your SQL worked like a charm !!!!! Thank you !!

    Do you have any ideas pertaining to my second question? Wondering if I can somehow modify your SQL to delete or clear whole fields or parts of fields?

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Questions (97 SR2)

    Glad to hear the statement worked for you. For your MANHRS question, think of it as retaining the first 3 characters rather than deleting the last 4 characters. Try the following statement:
    <pre>UPDATE tblIFUpdate SET tblIFUpdate.MANHRS = Left([MANHRS],3);
    </pre>

    Keep in mind there are no criteria here, so it will do this to all the records. Is that what you want?

    For your BK4 question, try this statement:
    <pre>UPDATE tblIFUpdate SET tblIFUpdate.BK4 = Null
    WHERE (((tblIFUpdate.BK4) Is Not Null));
    </pre>

    This will reset all BK4 values to Null. Be sure this is what you want.

    Again, replace all the tablenames and hold onto that backup.

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Indianapolis, Indiana
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query Questions (97 SR2)

    Thomas, right after I posted my reply to your answer, I began playing with your SQL. I made it happen. Here it is, and it works like a charm!! Thanks again!!

    UPDATE t_DIX_Bak SET t_DIX_Bak.MANHRS = Left([MANHRS],3) & "", t_DIX_Bak.BK4 = Left([BK4],0) & ""
    WHERE (((t_DIX_Bak.BK4) Is Not Null));

Posting Permissions

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