vba access CINT limitations

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
BillTrAsked:
Who is Participating?
 
ScobberCommented:
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
 
BillTrAuthor Commented:
I have access 2007. Perhaps I need to check the length on the string before trying to convert...
0
 
ScobberCommented:
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
 
BillTrAuthor Commented:
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
 
DhaestCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.