Forum Discussion

Eric_1226's avatar
Eric_1226
Copper Contributor
Jul 19, 2021

Left Function in Access Doesn't Count Decimal Point As a Character

So I am tryting to do Select

Right(Left([NUMBER]*0.65-Int([NUMBER]*0.65),4),1)

to get the 4th character ( so the 2nd decimal place, e.g. to return {3} from {1.234}) of this resulting number in a query. I found that it would actually return {4} (that is, the 5th character) because the Left function didn't count decimal point as a character. The funniest thing is I copied this function straight from collegues and it worked just as expected on every others' devices. I don't think I have ever touched any settings even though I think its something woring in the settings.

Any help please:( Thanks a lot.

  • Gustav_Brock's avatar
    Gustav_Brock
    Brass Contributor

    Eric_1226 : I can't replicate your issue. But, that said, always handle numbers as numbers, not text.

    Thus:

    Digit2 = Int([NUMBER]*0.65*100) Mod 10

     

    • isladogs's avatar
      isladogs
      MVP
      @Gustav.
      Thanks.That's even better.
      I tried to 'Like' your reply but can't yet work out how to do so on this site

      Eric
      I don't believe its the cause of your issue but NUMBER is a reserved word in Access and shouldn't be used for a field name
    • Eric_1226's avatar
      Eric_1226
      Copper Contributor
      Thanks. That's great stuff. I am guessing that's a MS version thing? Mine's 2016.
  • It gives the correct results for me.
    BTW you can simplify the expression to Mid([N]*0.65-Int([N]*0.65),4,1)