overflow error 6

G

Guest

I keep getting this error message when running the following code, which is
designed to count the number of records for a given ID in a given year, and
write that number in the appropriate field in a table. The whole code goes
from 1987-2006, but I have truncated it to 1987-1989 for assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the error is
indicated.

Thanks,

LAF
 
A

Allen Browne

Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.
 
M

mcescher

LAF,
When the error message pops up, click 'Debug'. Then hover over Yr87.
It will show you the value of the variable at that time. My guess
would be that it is 32767. Integers have a maximum of 32767.

So, if your needs are over that, use a Long, they have a max of
2147483647. Otherwise, check the code farther up to see if it's really
working correctly. If it's not, you will get the same "Overflow Error
6" message, but it will take longer to get there.

mcescher
 
G

Guest

Thanks, Allen

However, the meximum number of cases for a given year is 12, so there must
be some other problem to get the error.

LAF



Allen Browne said:
Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LAF said:
I keep getting this error message when running the following code, which is
designed to count the number of records for a given ID in a given year,
and
write that number in the appropriate field in a table. The whole code goes
from 1987-2006, but I have truncated it to 1987-1989 for assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the error is
indicated.

Thanks,

LAF
Click to expand...
Click to expand...
 
M

mcescher

What value do you get when you hover over the variable after you've
entered the debug mode?
32767 = logic errors above.
Click to expand...

mcescher


Thanks, Allen

However, the meximum number of cases for a given year is 12, so there must
be some other problem to get the error.

LAF



Allen Browne said:
Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LAF said:
I keep getting this error message when running the following code, which is
designed to count the number of records for a given ID in a given year,
and
write that number in the appropriate field in a table. The whole code goes
from 1987-2006, but I have truncated it to 1987-1989 for assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the error is
indicated.

Thanks,

LAF
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks, mcescher

I indeed do get a value of 32767 when hovering over that variable. I'll
check on the Do Until rst1.EOF, but I have used a program similar to this
before without getting the overflow error.

mcescher said:
What value do you get when you hover over the variable after you've
entered the debug mode?
32767 = logic errors above.
Click to expand...

mcescher


Thanks, Allen

However, the meximum number of cases for a given year is 12, so there must
be some other problem to get the error.

LAF



Allen Browne said:
Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I keep getting this error message when running the following code, which is
designed to count the number of records for a given ID in a given year,
and
write that number in the appropriate field in a table. The whole code goes
from 1987-2006, but I have truncated it to 1987-1989 for assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the error is
indicated.

Thanks,

LAF
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Is there meant to be an:
rst2.MoveNext
inside that loop?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LAF said:
Thanks, mcescher

I indeed do get a value of 32767 when hovering over that variable. I'll
check on the Do Until rst1.EOF, but I have used a program similar to this
before without getting the overflow error.

mcescher said:
What value do you get when you hover over the variable after you've
entered the debug mode?
32767 = logic errors above.
Click to expand...

mcescher


Thanks, Allen

However, the meximum number of cases for a given year is 12, so there
must
be some other problem to get the error.

LAF



:

Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.

I keep getting this error message when running the following code,
which is
designed to count the number of records for a given ID in a given
year,
and
write that number in the appropriate field in a table. The whole
code goes
from 1987-2006, but I have truncated it to 1987-1989 for
assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error
occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should
make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the
error is
indicated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Allen and mcescher,

The rst2.MoveNext did the trick. Also, the query did not do what the
program did. Program Mark is a sophisticated mark-recapture analysis program
for demographic studies. It requires an input file in which each ID
(Bandnum) has a row and in which there is one field for each year of the
study. That field has a 0 if the indiviudal was not captured and a 1 if it
is. The query only counts the years in which an individual was captured and
how many times. The program fills in the appropriate 0's.

Thanks again,

LAF



Allen Browne said:
Is there meant to be an:
rst2.MoveNext
inside that loop?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LAF said:
Thanks, mcescher

I indeed do get a value of 32767 when hovering over that variable. I'll
check on the Do Until rst1.EOF, but I have used a program similar to this
before without getting the overflow error.

mcescher said:
What value do you get when you hover over the variable after you've
entered the debug mode?

32767 = logic errors above.

mcescher



LAF wrote:
Thanks, Allen

However, the meximum number of cases for a given year is 12, so there
must
be some other problem to get the error.

LAF



:

Try using a Long instead of an Integer:
Dim Yr87 As Long
Integers overflow after 32767.

You could do this with a query.
Depress the Total button on the toolbar in query design.
Type this into the Field row: Year([EvDate])
Group by this field, and count the primary key.

I keep getting this error message when running the following code,
which is
designed to count the number of records for a given ID in a given
year,
and
write that number in the appropriate field in a table. The whole
code goes
from 1987-2006, but I have truncated it to 1987-1989 for
assistance.

Sub ProgramMarkFile()
Dim db As DAO.Database
Dim rst1 as Recordset
Dim rst2 as Recordset
Dim CurrentID As String
Dim TempYr as Integer
Dim sSQL as String
Dim Yr87 As Integer
Dim Yr88 As Integer
Dim Yr89 As Integer
Set db = CurrentDb()
Set rst2= db.OpenRecordset("AkepMark",dbOpenDynaset
rst2.Sort = "[BANDNUM],[EvDate]"
Set rst2 = rst2.OpenRecordset
Set rst1 = db.OpenRecordset("MarkAkepInPut", dbOpenDynaset)
Yr87=0
Yr88=0
Yr89=0
rst1.MoveFirst
Do Until rst1.EOF
CurrentID = rst1!Bandnum
sSQL = "[BANDNUM] = '" & CurrentID & "'"
rst2.FindFirst sSQL
Do While rst2!Bandnum = CurrentID
TempYr = Year(rst2!EvDate)
Select Case TempYr
Case 1987
Yr87 = Yr87 + 1 ## This the line where the error
occurs
Case 1988
Yr88 = Yr88 + 1
Case 1989
Yr89 = Yr89 + 1
End Select
Loop
rst1.Edit
rst1!Y87 = Yr87
rst1!Y88 = Yr88
rst1!Y89 = Yr89
rst1.Update

rst1.MoveNext
Yr87=0
Yr88=0
Yr89=0

Loop

Then a couple of closing and nothing statements.

I think that all the variables I have defined as integers should
make the
statement Yr87 = Yr87 + 1 also an integer. But this is where the
error is
indicated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

run time error 6 overflow 5
FindFirst and FindLast 3
No current record error 3021 1
consistent error on first record 11
Sort Question 15
string delimiters not recognized 2
How to make the part of the field name as a variable in a recordset 1
What is wrong with my coding? 2

Top