How to update numbers in a string based on a position number

[DaveT99]

I have a table of 8 digit numbers
I would like to update the last 2 digits to "00"
and would then copy to a new field and then update digit positions 5 & 6 to "00"



Foolishly I tried ??????00

can anyone help
many Regards

[Welshgasman]

Left(YourField,6) & "00"
Why are you updating 5 & 6 to 0, when you said last 2 digits of an 8 digit field?

If you are also changing 5 and 6, then just adjust the values above.

Create a query with the adjusted values as an extra column to check.

[CJ_London]

if it is a number you can use

?(12345478\100)*100
12345400

and similarly for your next stage
?(12345400 \10000)*10000
12340000

[DaveT99]

Thanks trying to do, unsuccessfull so far ha ha

But thanks for the second tip

You are so helpfull

[DaveT99]

My field name is L3 and am Struggling here I have tried to change just the last character i.e. Left(L3,8) & "0" without success

do I paste this code into SQL or in the query view and do I use an update query?????

Sorry for my ignorance

will try the 2nd solution now; they are all numeric values

[DaveT99]

Hi CJ
have tried without success, do I paste the above code in SQL view or Design View

Sorry
Dave

[Gicu]

To actually update the values you would use an update query and have the expressions given to you in the UpdateTo row in query design.
Cheers,

[CJ_London]

My field name is L3 and am Struggling here I have tried to change just the last character i.e. Left(L3,8) & "0" without success

well if there are only 8 character then all you are doing is appending a 0.

Also 'without success' does not help us to help you. What does 'without success' mean?

[Welshgasman]

My field name is L3 and am Struggling here I have tried to change just the last character i.e. Left(L3,8) & "0" without success

do I paste this code into SQL or in the query view and do I use an update query?????

Sorry for my ignorance

will try the 2nd solution now; they are all numeric values

Look up the syntax for the Left() function, as for an 8 digit field Left(L3,8) & "0" is nonsense. That just makes a 9 digit field.

[DaveT99]

To actually update the values you would use an update query and have the expressions given to you in the UpdateTo row in query design.
Cheers,

Thanks Vlad, did try that but will retry as it did update the last 2 digits but lost the first 6 characters

Dave

[Gicu]

That is why you try it first as a calculated field in a select query to view it side by side with the original field and if happy with what you see then you take the expression and use it in the update.

Cheers,

[DaveT99]

That is why you try it first as a calculated field in a select query to view it side by side with the original field and if happy with what you see then you take the expression and use it in the update.

Cheers,

Thanks everyone for all you messages, especially CJ where he said what does without success mean!!!

Here is a pdf that shows the original data, the update query and the result of running the query

what am I doing wrong???

Regards
dave

[Welshgasman]

You have Left("L3",6)& "00
You are using L3 as a literal, literally the word L3, not the field L3

Look at my syntax. Replace YourField with L3. No quotes are required except for the 00

[Micron]

and would then copy to a new field and then update digit positions 5 & 6 to "00"

Where? Maybe I could figure that out on my own but I'd rather not have to download, unzip and poke around in your file to try to figure that out.

If it's an 8 digit field and you update digits 7 and 8 to 00 and then digits 5 and 6 to 00 then why not just change the last four to 0000 in either the new field or the existing one? Then it can just be myField = (myField\1000)*1000 as CJ indicated. NOTE: you must use \ and not /.

Left() is a string function and I don't see that working if your field is actually numeric. I think that function can only return a string, so you will likely get a data type mismatch error if you try to use it.

[Welshgasman]

In fact, as you are doing it to all the records, you could say it is calculated, and leave the original data well alone?