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

[DaveT99]

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

Thanks again for your response

Access is inserting the QUOTES after running the query

Not sure what you mean "you are using L3 as a literal....."

Have tried again Left(L3,6)& "00

But same issue, am stumpt, obviously I am doing something wrong

[DaveT99]

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.

cant do that as the final output will be 3 columns

e,g
Col 1 Col 2 Col 3
31401503 31401500 31400000

Cheers
Dave

[Welshgasman]

Just tested on a numeric field and no complaints.

UPDATE [old Transactions] SET [old Transactions].Amount = Left([amount],2) & "00";

[Gicu]

Have a look at the attached file please.
Cheers,

[Welshgasman]

What is the purpose of this?
You can just take the first 4 characters and append "0000" to that.

[mike60smart]

Hi Dave

Look at query1 in the attached.

I used CJ_London's example

[CJ_London]

from your pdf.

1. it looks like this is a text field since the values are orientated to the left - so my method is unlikely to work
2. when using the query builder, you need to use square brackets i.e. [L3] to indicate it is a field and not literal text

so this
Left(L3,6)& "00

becomes this

Left([L3],6)& "00"

Not sure what you mean "you are using L3 as a literal....."

hope that is now clear