If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Numeric Field Overflow Error for Macro
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. |
#2
|
|||
|
|||
Numeric Field Overflow Error for Macro
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 he 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message ups.com... 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. |
#3
|
|||
|
|||
Numeric Field Overflow Error for Macro
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. |
#4
|
|||
|
|||
Numeric Field Overflow Error for Macro
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. wrote in message ups.com... 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. |
#5
|
|||
|
|||
Numeric Field Overflow Error for Macro
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. |
Thread Tools | |
Display Modes | |
|
|