Solved

vba access CINT limitations

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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
equalIsNot  challenge 43 167
maxBlock challenge 30 142
why to use string 10 61
only14 challenge 19 81
Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This video teaches viewers about errors in exception handling.
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.

808 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