Overflow Error with DMin Function in Access VBA

Question

I'm using DMin function in Ms-Access VBA, as below Dim MinVal as integer MinVal = DMin("ARRID", "Tbl_abc", "[RequestStatus] = 1 AND [WorkInProgress] = -1")

The above function was working fine untill now for almost a year, and all of a sudden it is giving me Overflow Error. i'm doing this Dmin on an Auto number, so there is no chance of Null/zeros. Ideally the MinVal should be 40316 from the data which is not big number as well. I have tried compacting and reparing the database. still the problem persist.However, when i run the above DMin from an access query it is working fine. it is only in VBA it is not picking up.

Any help will be appriciated.

Answer 1

You need to change Dim MinVal As Integer to Dim MinVal As Long. The maximum value of Integer (16-bits)is 32,767, whereas the max for Long (32-bits) is over 2 billion. Access stores Autonumbers as 32-bit Longs internally, so the queries have always worked. When you finished work on the last request that had ARRID <=32767, your VBA had to put a 32-bit value into a 16-bit box. Overflow is your warning that the conversion would be incorrect. Make your box bigger!