Solved

vba access CINT limitations

Posted on 2012-12-21
6
477 Views
Last Modified: 2013-01-26
What is the best approach for convering large numbers from text to numeric in access? I have some code where I used CINT but found in cases where the number is very large it drops out. I experimented with cdlb and clng but the still had issues.

My intent was to convert to numeric so I could apply an allowance when I compare between the 2 tables. So, if something was greater than say 2% I would flag it as an error but less than that I would allow.

Thanks
0
Comment
Question by:BillTr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
Scobber earned 300 total points
ID: 38715462
try TRIM and CLNG, (Cast Long Integer)

If your database is returning a number as a string. you may need to trim the whitespace off the edges

dim newvariable as long
newvariable = clng(trim(variable))

Another method I have used in the past was to multiply or divide it.

dim newvariable as long
newvariable = variable * 1

Tis may be down to your version of access too. I cant break 2013 in this way.

HTH Scobber
0
 

Author Comment

by:BillTr
ID: 38717201
I have access 2007. Perhaps I need to check the length on the string before trying to convert...
0
 
LVL 7

Assisted Solution

by:Scobber
Scobber earned 300 total points
ID: 38729847
You could also use as try and catch statement

Try
e=long(value)
catch ex as exception
msgbox "I failed on value"
end try

Open in new window

or to ignore errors all together
Try
e=long(value)
catch ex as exception
end try

Open in new window

0
 

Author Comment

by:BillTr
ID: 38816454
I was able to circumvent the problem by changing the datatype on the import. I had been defaulting everything to text on the imports. I reworked each import to use the correct data type and avoided having to convert in VBA.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 38821774
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
format the code in java 6 110
countEvens challenge 2 115
wordappend challenge 8 223
Change to event 1 129
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
This article will show, step by step, how to integrate R code into a R Sweave document
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question