update one digit in a larger number for a specific series

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a series of 100 numbers in my table that have one digit incorrect..it
is the third digit in a six-digit number, and all will be changed to the same
digit (ie. they are all 4678## and need to be 4676##.) The last two numbers
vary...I cannot lose them.
Can I do this programmatically ? I figure it's some form of update query I
could just manually change each one but I expect I could run into this again.
I have found this discussion group to be a lifesaver for me, not being a
anywhere close to a programmer!
Any suggestions are helpful!
 
Hi,
I have a series of 100 numbers in my table that have one digit incorrect..it
is the third digit in a six-digit number, and all will be changed to the same
digit (ie. they are all 4678## and need to be 4676##.) The last two numbers
vary...I cannot lose them.
Click to expand...

This field should almost certainly be stored as a Text field rather
than a number, unless you'll be doing arithmatic with the value. If it
were, you could use the Left() and Mid() functions to pull the first
three and last two digits off. With number fields you'll need to use
some trickier expressions (below).
Can I do this programmatically ? I figure it's some form of update query I
could just manually change each one but I expect I could run into this again.
Click to expand...

Air code... try this AFTER YOU BACK UP YOUR DATABASE, and check the
results carefully: update to

100 * ([field] \ 100 - 2) + [Field] MOD 100

Taking this from the inside out:

[field] \ 100

does an *integer* divide, so 467832 comse to 4678

Subtracting 2 from this value gives 4676

Multiplying by 100 gives 467600

The MOD operator returns the remainder after dividing by 100, e.g.
467823 MOD 100 is 23

Adding the original value of the field modulo 100 gives 467623


John W. Vinson[MVP]
 
Thanks so much, yes, it worked fine.
I have set the field to number because I often need to sort on this field
and text sorts just don't work well. It's also the primary key field, and I
wanted to keep it to a number to prevent error in the data entry (we have 3-4
different operators at any one time) over a period of 3 months, they enter
15 000 records.
Is there a better way I should have designed this?

G.
 
Thanks so much, yes, it worked fine.
I have set the field to number because I often need to sort on this field
and text sorts just don't work well. It's also the primary key field, and I
wanted to keep it to a number to prevent error in the data entry (we have 3-4
different operators at any one time) over a period of 3 months, they enter
15 000 records.
Is there a better way I should have designed this?

G.
Click to expand...

If you use an Input Mask of 00000000 on a Text field the operators
will be constrained to always enter eight numeric digits, and the
field will sort correctly. I don't see how having the field numeric
affects data entry errors; it's just as easy to type 123546 instead of
123456 into a Number field as into a Text field!

John W. Vinson[MVP]
 
Back
Top