MS Access VBA odd Overflow error

Check out this code:

Dim lngInterval As Long
  lngInterval = 1 * 60 * 1000

Open in new window


I'm getting an Overlow error. Why would that be?

BTW, if I go to the Immediate window and type ?1 * 60 * 1000, I also get an overflow error.

In fact, ?60 * 1000 in the immediate window gives the overflow error.

I've check Tools | References and nothing is MISSING.

This is messed up, right? Anyone know how to fix this?
KapriceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KapriceAuthor Commented:
Changing the code to

Dim lngInterval As Long
  lngInterval = 60000

Open in new window


works just fine.
Benjamin HelfmanCommented:
Public Function Test() As Long

     Dim lng as Long
     lng = (1*60)
     lng = lng * 1000
     Test = lng

End Function

Open in new window


Put that in a Module and type ? Test in the immediate window.  I get 60000 as out put.  However, when I attempted to assign in one step, I too got the overflow error.
Rey Obrero (Capricorn1)Commented:
try this

Dim lngInterval As Long
  lngInterval = clng(1) * 60 * 1000

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

KapriceAuthor Commented:
I'll try that, but recall that I also tried 60 * 1000 and got the same error.
KapriceAuthor Commented:
Funny. clng(1) * 60 * 1000 worked, even though 60 * 1000 does not. Any explanation as to why?
Rey Obrero (Capricorn1)Commented:
try
 lngInterval = clng(60) * 1000
Robert ShermanOwnerCommented:
(EDIT: Just providing an answer to the why?  not looking for any points, but was curious myself and looked for the answer...)

Explanation taken from Robert Gamble at http://stackoverflow.com/questions/316312/misunderstanding-of-long-data-type-in-vba

2000 and 365 are Integer values. In VBA, Integers are 16-bit signed types, when you perform arithmetic on 2 integers the arithmetic is carried out in 16-bits. Since the result of multiplying these two numbers exceeds the value that can be represented with 16 bits you get an exception. The second example works because the first number is first converted to a 32-bit type and the arithmetic is then carried out using 32-bit numbers. In your example, the arithmetic is being performed with 16-bit integers and the result is then being converted to long but at that point it is too late, the overflow has already occurred. The solution is to convert one of the operands in the multiplication to long first:
Rey Obrero (Capricorn1)Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.