Update Query Questions (97 SR2)

Topic

New Reply

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 . 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 .
UPDATE t_DIX_Bak SET t_DIX_Bak.APC = IIf(“EOE”=”4140″,”??OA”,”APC”)
WHERE (((t_DIX_Bak.EOE)=”4140″));

Regards,

Bob in Indy

Reply | Quote

Replies

You can update the records as you describe in your first question with the following Update query:

UPDATE tblIFUpdate SET tblIFUpdate.APC = Left([APC],2) & "OA"
WHERE (((tblIFUpdate.EOE)="4140"));

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

Reply | Quote

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?

Reply | Quote

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:

UPDATE tblIFUpdate SET tblIFUpdate.MANHRS = Left([MANHRS],3);

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:

UPDATE tblIFUpdate SET tblIFUpdate.BK4 = Null
WHERE (((tblIFUpdate.BK4) Is Not Null));

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.

Reply | Quote

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));

Reply | Quote