DSum Works in Field Control but Overflows error 6 in VBA

Question

I have a combobox with about 11 user names for options.

On Update a tempTable is created from a make table query.

(The make table query uses data from a larger static table that originated from excel.) There are no blanks, no nulls, no decimals, just integers

Within the tempTable is a column for "NetWorkDays" <-Calculated in excel first.

My Static Table and tempTable were originally set to integer. Now my tables is set to long integer.

Still I get run-time error 6 overflow.

The kicker is ....it only overflows for one of the combo box options.

Even more confusing to me is that I can enter the same =DSum into a txtBox control on the form and it gets the sum from the tempTable data just fine!?

the tempTable has only 600 rows and the NetWorkDays colunn total is actually about 44600. From another formun I read that the Biggest Integer in VBA is 32767, which is why I switched to long....which did not help.

In Short

Either of the options below:

TOTALDAYS = Int(DSum("[NetWrkDays]", "tempTable"))

TOTALDAYS = DSum("[NetWrkDays]", "tempTable")

with:

Tables set to Integer or Long

Result:

In VBA error 6 , for one comboBox option. but the desired answer can be found from txtBox control on the form using same temptable and DSum formula?

Not sure what I am missing here. My guess is the large sum value, but thought long would handle that....also why does it work in a control and not in vba?

Thanks for any insight on why a situation would only fail in VBA?

Answer 1

Dim TOTALDAYS as Long should solve this issue?