New Contributor

# 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.

7 Replies

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

It gives the correct results for me.
BTW you can simplify the expression to Mid([N]*0.65-Int([N]*0.65),4,1)

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

@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``

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

@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

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

Success. Post successfully liked!

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

@isladogs Thank you guys. Those were valuable recommendations. Though NUMBER was just an example. All of your simplifications would definitely get the job done but I would still very much like to figure out why my device keeps returning different result from other devices and why the LEFT function don't count decimal points as other devices do. I have like 20 different Access and it looks like I will just have to change code for em all.

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

Thanks. That's great stuff. I am guessing that's a MS version thing? Mine's 2016.

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

No. It is a Windows setting.