Help on "Overflow error" due to my poor Memory Var handling or other unknown

E

EagleOne

2003

The routines which follow work fine with 15,000 records.

When the file size gets larger I do get overflow errors.

Background: The VBA routines up to this point have read text files into two Tables.

Table one "STARS" Table two "CHOOSE"

All has been successful until I decided to save Record Counts and Dollar totals by Category. My
desire was to use the MemVars to transfer into an Excel w/s to be created below the following code.

There are about 40 total MemVars 20 record counts and 20 $ (sub) totals by category.

I am sure that my routines are poorly (not smartly) handled and I need ASARP help as to the best way
to either save the MemVars (in groups of i.e 5?) to a file (another Table?) and/or clear the MemVars
and then go on to the next 5 MemVars until 40 is reached.

All this is being done in VBA (SQL).

I have for sure ID'd the area of overflow to the following memvar section.

Sample Code:

strSQL = "SELECT COUNT(*) AS R5CCount FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseCount = IIf(IsNull(rsCurr!R5CCount), 0, rsCurr!R5CCount)

strSQL = "SELECT Sum([AMT]) AS R5CAMT FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseAMT = IIf(IsNull(rsCurr!R5CAMT), 0, rsCurr!R5CAMT)

strSQL = "SELECT COUNT(*) AS R6CCount FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseCount = IIf(IsNull(rsCurr!R6CCount), 0, rsCurr!R6CCount)

strSQL = "SELECT Sum([AMT]) AS R6CAMT FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseAMT = IIf(IsNull(rsCurr!R6CAMT), 0, rsCurr!R6CAMT)



...... This continues until 40 Memvars are created ......

As mentioned, the logic works but it blows up with big files

What steps should I take to do the above smartly? Beggers can not be choosers - but help soon
please.

I do not need help with Automation etc, all that works fine.

TIA

EagleOne
 
J

John Spencer

Did you declare the types of the memory variables? If not, they could
be defaulting to integer (max 32K) or single.

Try declaring the variable types at the top of your code

Dim Reg5ChooseCount as Long , Reg6ChooseCount as Long
Dim Reg5ChooseAMT as Double, Reg6ChooseAmt as Double




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
E

EagleOne

Do not ask my why but I did:

Dim Counts as Integers (Blaaaaah Ug)
Dim AMT's as Currency

Could that have been the problem? Or should I consider anything else?

Thanks for your time and knowledge

John Spencer said:
Did you declare the types of the memory variables? If not, they could
be defaulting to integer (max 32K) or single.

Try declaring the variable types at the top of your code

Dim Reg5ChooseCount as Long , Reg6ChooseCount as Long
Dim Reg5ChooseAMT as Double, Reg6ChooseAmt as Double




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


[email protected] said:
2003

The routines which follow work fine with 15,000 records.

When the file size gets larger I do get overflow errors.

Background: The VBA routines up to this point have read text files into two Tables.

Table one "STARS" Table two "CHOOSE"

All has been successful until I decided to save Record Counts and Dollar totals by Category. My
desire was to use the MemVars to transfer into an Excel w/s to be created below the following code.

There are about 40 total MemVars 20 record counts and 20 $ (sub) totals by category.

I am sure that my routines are poorly (not smartly) handled and I need ASARP help as to the best way
to either save the MemVars (in groups of i.e 5?) to a file (another Table?) and/or clear the MemVars
and then go on to the next 5 MemVars until 40 is reached.

All this is being done in VBA (SQL).

I have for sure ID'd the area of overflow to the following memvar section.

Sample Code:

strSQL = "SELECT COUNT(*) AS R5CCount FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseCount = IIf(IsNull(rsCurr!R5CCount), 0, rsCurr!R5CCount)

strSQL = "SELECT Sum([AMT]) AS R5CAMT FROM ChooseRev WHERE [Reg_Numb]= '05'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg5ChooseAMT = IIf(IsNull(rsCurr!R5CAMT), 0, rsCurr!R5CAMT)

strSQL = "SELECT COUNT(*) AS R6CCount FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseCount = IIf(IsNull(rsCurr!R6CCount), 0, rsCurr!R6CCount)

strSQL = "SELECT Sum([AMT]) AS R6CAMT FROM ChooseRev WHERE [Reg_Numb]= '06'"
Set rsCurr = dBs.OpenRecordset(strSQL)
Reg6ChooseAMT = IIf(IsNull(rsCurr!R6CAMT), 0, rsCurr!R6CAMT)



...... This continues until 40 Memvars are created ......

As mentioned, the logic works but it blows up with big files

What steps should I take to do the above smartly? Beggers can not be choosers - but help soon
please.

I do not need help with Automation etc, all that works fine.

TIA

EagleOne
Click to expand...
Click to expand...
 
T

Tom Wickerath

Integer
Stores numbers from –32,768 to 32,767 (no fractions).

Long Integer (ie. Long)
Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).

Single
Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from
1.401298E–45 to 3.402823E38 for positive values.

Double
Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324
for negative values and from 4.94065645841247E–324 to 1.79769313486231E308
for positive values.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Top