Update Query-Replacing 1st number with another

[indyaries]

Greetings,

I've searched on Update Queries, but didn't find anything to do this.

I am using Access 97, SR2

I have a text field called BLK, length is 3.

I need to replace the first character with the number 9, regardless of what the other two characters are.

Example:

Current data: 0L3
Replaced data: 9L3

I've tried using a Left argument, but could not get it to work.

Thanks in advance,

Bob in Indy

 

[Jon K]

Run this update query:-

UPDATE yourTable SET BLK="9" & Mid(BLK,2)

 

[indyaries]

Works like a charm !!

Thanks, Jon. Just what I needed.

 

[indyaries]

A new wrinkle

Jon (Or anyone else),

This is in the same database, different field. I've tried adapting the SQL you provided, but I'm getting a syntax error.

I have a field called EOE (Element of Expense). It's a text field, length 4.

I need an update query that will change all EOE <> 1198, 1199, 1250 to 1198.

So...if the EOE Is Not 1198 Or 1199 Or 1250, change the EOE to 1198.

In this example, there are 750 records...of which 453 ARE NOT 1198, 1199, or 1250. Its these 453 records that I want to change to 1198.

Here is the SQL from a Select query where I have the above criteria:

WHERE (((t_IDT_0.EOE) Not Like "1198" And (t_IDT_0.EOE) Not Like "1199" And (t_IDT_0.EOE) Not Like "1250"));

I don't know how to write the SQL that will implement the changes I need.

Thanks in advance!

Bob in Indy

 

[Jon K]

The Update Query:

UPDATE t_IDT_0 SET EOE = "1198"
WHERE EOE Not Like "1198" And EOE Not Like "1199" And EOE Not Like "1250";

 

[indyaries]

I'll try it tomorrow...!

Thanks again, Jon. I'll try it out tomorrow at work.

Sincerely,

Bob in Indy