Solved

vba access CINT limitations

Posted on 2012-12-21
6
460 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
FizzBuzz challenge 9 76
fix34  challenge 9 104
Copy Files - Python 7 71
Re-position the objects 7 96
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…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now