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

%3CLINGO-SUB%20id%3D%22lingo-sub-2561350%22%20slang%3D%22en-US%22%3ELeft%20Function%20in%20Access%20Doesn't%20Count%20Decimal%20Point%20As%20a%20Character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2561350%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20am%20tryting%20to%20do%20Select%3C%2FP%3E%3CP%3ERight(Left(%5BNUMBER%5D*0.65-Int(%5BNUMBER%5D*0.65)%2C4)%2C1)%3C%2FP%3E%3CP%3Eto%20get%20the%204th%20character%20(%20so%20the%202nd%20decimal%20place%2C%20e.g.%20to%20return%20%7B3%7D%20from%20%7B1.234%7D)%20of%20this%20resulting%20number%20in%20a%20query.%20I%20found%20that%20it%20would%20actually%20return%20%7B4%7D%20(that%20is%2C%20the%205th%20character)%20because%20the%20Left%20function%20didn't%20count%20decimal%20point%20as%20a%20character.%20The%20funniest%20thing%20is%20I%20copied%20this%20function%20straight%20from%20collegues%20and%20it%20worked%20just%20as%20expected%20on%20every%20others'%20devices.%20I%20don't%20think%20I%20have%20ever%20touched%20any%20settings%20even%20though%20I%20think%20its%20something%20woring%20in%20the%20settings.%3C%2FP%3E%3CP%3EAny%20help%20please%3A(%20Thanks%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2561350%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563636%22%20slang%3D%22en-US%22%3ERe%3A%20Left%20Function%20in%20Access%20Doesn't%20Count%20Decimal%20Point%20As%20a%20Character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563636%22%20slang%3D%22en-US%22%3EIt%20gives%20the%20correct%20results%20for%20me.%3CBR%20%2F%3EBTW%20you%20can%20simplify%20the%20expression%20to%20Mid(%5BN%5D*0.65-Int(%5BN%5D*0.65)%2C4%2C1)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2569867%22%20slang%3D%22en-US%22%3ERe%3A%20Left%20Function%20in%20Access%20Doesn't%20Count%20Decimal%20Point%20As%20a%20Character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2569867%22%20slang%3D%22en-US%22%3E%40Gustav.%3CBR%20%2F%3EThanks.That's%20even%20better.%3CBR%20%2F%3EI%20tried%20to%20'Like'%20your%20reply%20but%20can't%20yet%20work%20out%20how%20to%20do%20so%20on%20this%20site%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1058532%22%20target%3D%22_blank%22%3E%40Eric%3C%2FA%3E%3CBR%20%2F%3EI%20don't%20believe%20its%20the%20cause%20of%20your%20issue%20but%20NUMBER%20is%20a%20reserved%20word%20in%20Access%20and%20shouldn't%20be%20used%20for%20a%20field%20name%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2573519%22%20slang%3D%22en-US%22%3ERe%3A%20Left%20Function%20in%20Access%20Doesn't%20Count%20Decimal%20Point%20As%20a%20Character%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1007894%22%20target%3D%22_blank%22%3E%40isladogs%3C%2FA%3E%26nbsp%3BThank%20you%20guys.%20Those%20were%20valuable%20recommendations.%20Though%20NUMBER%20was%20just%20an%20example.%20All%20of%20your%20simplifications%20would%20definitely%20get%20the%20job%20done%20but%20I%20would%20still%20very%20much%20like%20to%20figure%20out%20why%20my%20device%20keeps%20returning%20different%20result%20from%20other%20devices%20and%20why%20the%20LEFT%20function%20don't%20count%20decimal%20points%20as%20other%20devices%20do.%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fhtml%2F%40C7BE33135EA1742D4E0CF1FF00DD009F%2Fimages%2Femoticons%2Fcryingwhilelaughing_anim.gif%22%20alt%3D%22%3Acryingwithlaughter%3A%22%20title%3D%22%3Acryingwithlaughter%3A%22%20%2F%3E%20I%20have%20like%2020%20different%20Access%20and%20it%20looks%20like%20I%20will%20just%20have%20to%20change%20code%20for%20em%20all.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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
It gives the correct results for me.
BTW you can simplify the expression to Mid([N]*0.65-Int([N]*0.65),4,1)

@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

 

@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
Success. Post successfully liked!

@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.:cryingwithlaughter: I have like 20 different Access and it looks like I will just have to change code for em all.

Thanks. That's great stuff. I am guessing that's a MS version thing? Mine's 2016.
No. It is a Windows setting.