Solved

vba access CINT limitations

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
either24  challenge 19 97
zeroMAx challenge 20 101
Specific format 21 198
WPF - Tooltips for ComboBox items 5 72
Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
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 explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

777 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