Numeric Field Overflow Error for Macro

[DoveArrow]

Using Access 2003, I have a table linked to an Excel spreadsheet.
Running a series of append queries as part of a macro, I'm trying to
generate a table that pulls up the E-Mail addresses of students'
advisors so that I can run a Mail Merge. If I run this macro once, it
works fine. If I try to run the macro again, it gives me a "Numeric
field overflow" error. If I close the Access Database altogether and
open it back up again, the macro again works fine, but only the one
time. Finally, if I go through and manually run each of the append
queries by double clicking on them in Queries, I can run the whole
process any number of times without ever getting the error message.

I'm sure you're going to need to know more information than that, but
I'm not exactly sure what you're going to want, so let me know and
I'll respond.

 

[Allen Browne]

Okay, the best we can do here is describe what's likely to cause the
message, and let you track down where it's coming from.

Numeric overflow usually means a number is outside its range of possible
values. The default type in Access is integer, so a value larger than 32767
can give the error.
You can demonstrate that if you press Ctrl+G to open the Immediate window,
and type:
? 200 * 200

To solve the problem, explicitly typecast to a larger type, e.g.:
? CLng(200) * 200
There's info on typcasting calculated fields here:
http://allenbrowne.com/ser-45.html

For an attached table, Access reads the first few rows to determine the data
type. If the last row in the spreadsheet is a sum of the numbers above, it
could overflow.

Part of your issue here in tracking this down is that each query
individually runs fine. I guess it will be a matter of temporarily
eliminating them from the macro, until you identify the one that gives the
problem. Then you still have the job of tracking down the field within that
query that has the overflow.

Another workaround might be to create a table with the data types large
enough to handle everything, and then import the data to work with.

Occassionally this error message indicates a problem with the way the
wizards or libraries are installed in Access.

Hope that at least gives you some ideas where to look and how to narrow it
down.

 

[DoveArrow]

Okay, I took your advice and started eliminating the individual
queries to see which one might be causing the problem and I figured
out which one it is. It's a query that takes a look at my base table
and looks specifically for students who attend a particular campus and
who do not have a date (any date) listed in a column called DAS Done.
My guess is that the problem has something to do with the criteria
that I've used for telling it what to pull from this DAS Done column.
Here's my SQL for the query. The one that I think is the offender is
the last line.

INSERT INTO [Contact E-Mail Base Table] ( [Student ID], [Last Name],
[First Name], [Academic Program], [Catalog], Status, Location, [Start
Term], [Admit Status], [Acad Level], [Admit Date], DAS )
SELECT [New Admits].[Student ID], [New Admits].[Last Name], [New
Admits].[First Name], [New Admits].[Academic Program], [New
Admits].Catalog, [New Admits].Status, [New Admits].Location, [New
Admits].[Start Term], [New Admits].[Admit Status], [New Admits].[Acad
Level], [New Admits].[Admit Date], [New Admits].DAS
FROM [New Admits]
WHERE ((([New Admits].Location)=101) AND (([New Admits].[DAS Done]) Is
Null));

Now if I'm right, how do I create a query that will handle the data in
these fields so that I don't the error message? Note: You may have
explained it already, but I'm a pretty big n00b when it comes to
Access, so a lot of what you said only made a little bit of sense to
me.

 

[Allen Browne]

I take it that [DAS Done] is a Date/Time field.
What is the data type of the Location field?
If Text, you need extra quotes.

If that's not the issue, then start looking for a mismatch on the field type
or size between the fields in [New Admits] and the fields in [Contact E-Mail
Base Table].

For example, if [Contact E-Mail Base Table].[Student ID] is a Number (size
Integer), but [New Admits].[Student ID] is a Number (size Long) then it
could overflow.

 

[DoveArrow]

If that's not the issue, then start looking for a mismatch on the field type

or size between the fields in [New Admits] and the fields in [Contact E-Mail
Base Table].

For example, if [Contact E-Mail Base Table].[Student ID] is a Number (size
Integer), but [New Admits].[Student ID] is a Number (size Long) then it
could overflow.

I think I figured it out. When the New Admits table pulls into Access,
the data in the Admit Date column pulls in as text. However, there
were about a dozen students from 06/01/06 who had dates listed in a
date/time format and it was gumming up the process.
I couldn't figure out how to shut off the automatic format in Excel
(it kept changing my dates to a date/time format), so it wasn't the
most elegant process trying to change the dates to a text format. In
the end, I had to copy the offenders from the New Admits table in
Access, create a new table, change the #NUM fields to 06/01/06, export
the table to a workbook in Excel, and then finally cut and paste these
Admit dates into the New Admits Excel file so that they'd have the
formatting I wanted. In any case, the program now runs just fine.

Thanks so much for your help. I really appreciate it.