update query expanding from 6 charactures to 7

[ifoundgoldbug]

Greetings

i need to change all of my records from abc123 to abc0123. I am trying
to figure out a query to make this work any help would be appreciated.

than you for your time

 

[ifoundgoldbug]

hmm that isn't quite what i need. it turns out i have a few
exceptions. so i need to change it to something like

IF (3rd character from left is a letter) Then
Left([YourFieldName],3) & "0" & Right([YourFieldName],3)
else
Left([YourFieldName],2) & "0" & Right([YourFieldName],4)


i am not sure how to pull this off. i assume that it is going to be
like
IF Mid([Tool #], 3) = a-z then <-- not sure how to do this

Backup your database first.

Update to ---

Left([YourFieldName],3) & "0" & Right([YourFieldName],3)

Greetings

i need to change all of my records from abc123 to abc0123. I am trying
to figure out a query to make this work any help would be appreciated.

than you for your time

 

[John Vinson]

hmm that isn't quite what i need. it turns out i have a few
exceptions. so i need to change it to something like

IF (3rd character from left is a letter) Then
Left([YourFieldName],3) & "0" & Right([YourFieldName],3)
else
Left([YourFieldName],2) & "0" & Right([YourFieldName],4)


i am not sure how to pull this off. i assume that it is going to be
like
IF Mid([Tool #], 3) = a-z then <-- not sure how to do this

Sounds like your Tool # field isn't atomic and wasn't ideally designed
initially! I take it you're running into Tool998 and you've got two
more to enter? <g>

Try running TWO separate update queries: one with a criterion of

LIKE "[A-Z][A-Z][A-Z]###"

to get the ABC123 values, and another

LIKE "[A-Z][A-Z]####"

to get the ZZ0000 entries. Adjust the criteria as needed.

John W. Vinson[MVP]

 

[ifoundgoldbug]

that is correct I am fixing someones lack of foresight :'( but I will
get it I hope that it works

hmm that isn't quite what i need. it turns out i have a few
exceptions. so i need to change it to something like

IF (3rd character from left is a letter) Then
Left([YourFieldName],3) & "0" & Right([YourFieldName],3)
else
Left([YourFieldName],2) & "0" & Right([YourFieldName],4)


i am not sure how to pull this off. i assume that it is going to be
like
IF Mid([Tool #], 3) = a-z then <-- not sure how to do this

Sounds like your Tool # field isn't atomic and wasn't ideally designed
initially! I take it you're running into Tool998 and you've got two
more to enter? <g>

Try running TWO separate update queries: one with a criterion of

LIKE "[A-Z][A-Z][A-Z]###"

to get the ABC123 values, and another

LIKE "[A-Z][A-Z]####"

to get the ZZ0000 entries. Adjust the criteria as needed.

John W. Vinson[MVP]

 

[John Vinson]

that is correct I am fixing someones lack of foresight :'(

Shades of the Y2K crisis... :-{)

Good luck, hope it works out well for you. Maybe you should put in
FIVE digits now while you're changing it? or split the field into an
alpha portion and a Long Integer number field?

John W. Vinson[MVP]