"Numeric field overflow."

Am regularly running into a problem where something snaps and an append
query stops running with the captioned error message. I'm not doing any
math calculations, just appending selected fields from a filtered record
set.

Once this happens, it stays broken until I edit the query (as in add a
field, then delete it, functionally taking me back to when I started) and
then the query works again for a while. It's driving me nuts because I
don't know how to identify the source, and I'm tired of having to futs
around with nonsense edits to the query to get it working again. Any ideas
what might be causing this??

I have the impression after having struggled with this and other oddball
Access things this week that Access 2003 SP2, under XP Pro with all updates,
is kind of buggy. Were earlier releases more stable?

Thanks,
George

An overflow indicates that a numeric value is outside the range of the field
Access is trying to append it to.

If the source query has calculated fields, you may need to typecast the
calculation with CDbl(), CCur(), CLng(), etc. You will probably need to use
Nz() inside that also, to avoid problems with Null.

Another possibility is that the field size in the target table is not
adequate. For example, a Number field of size Integer will not accept values
larger than 32767.

If the field sizes are not an issue, and typecasting does not solve the
problem, it is possible that Access is confused about what the query is
referrring to. Try unchecking the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
Info on why:
http://allenbrowne.com/bug-03.html

A2003 is not less stable than previous versions. The query engine (JET 4) is
actually the same for Access 2000, 2002, and 2003. This engine is somewhat
less efficient (for example with user-defined function calls) and less
capable of recognising data types (on calculated fields) than its
predecessor (JET 3.5 in Access 97), but it is not less stable.

It is possible to configure A2003 so it is arguably the most stable version
of Access, at least since version 2. For info on how to do that, see:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html

--
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.

"G Lykos" wrote in message
...
Am regularly running into a problem where something snaps and an append
query stops running with the captioned error message. I'm not doing any
math calculations, just appending selected fields from a filtered record
set.

Once this happens, it stays broken until I edit the query (as in add a
field, then delete it, functionally taking me back to when I started) and
then the query works again for a while. It's driving me nuts because I
don't know how to identify the source, and I'm tired of having to futs
around with nonsense edits to the query to get it working again. Any
ideas
what might be causing this??

I have the impression after having struggled with this and other oddball
Access things this week that Access 2003 SP2, under XP Pro with all
updates,
is kind of buggy. Were earlier releases more stable?

Thanks,
George

Allen, thanks for your thoughtful reply and specific suggestions for
pursuing more information.

Regards,
George


"Allen Browne" wrote in message
...
An overflow indicates that a numeric value is outside the range of the
field
Access is trying to append it to.

If the source query has calculated fields, you may need to typecast the
calculation with CDbl(), CCur(), CLng(), etc. You will probably need to
use
Nz() inside that also, to avoid problems with Null.

Another possibility is that the field size in the target table is not
adequate. For example, a Number field of size Integer will not accept
values
larger than 32767.

If the field sizes are not an issue, and typecasting does not solve the
problem, it is possible that Access is confused about what the query is
referrring to. Try unchecking the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
Info on why:
http://allenbrowne.com/bug-03.html

A2003 is not less stable than previous versions. The query engine (JET 4)
is
actually the same for Access 2000, 2002, and 2003. This engine is somewhat
less efficient (for example with user-defined function calls) and less
capable of recognising data types (on calculated fields) than its
predecessor (JET 3.5 in Access 97), but it is not less stable.

It is possible to configure A2003 so it is arguably the most stable
version
of Access, at least since version 2. For info on how to do that, see:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html

--
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.

"G Lykos" wrote in message
...
Am regularly running into a problem where something snaps and an append
query stops running with the captioned error message. I'm not doing any
math calculations, just appending selected fields from a filtered record
set.

Once this happens, it stays broken until I edit the query (as in add a
field, then delete it, functionally taking me back to when I started)
and
then the query works again for a while. It's driving me nuts because I
don't know how to identify the source, and I'm tired of having to futs
around with nonsense edits to the query to get it working again. Any
ideas
what might be causing this??

I have the impression after having struggled with this and other oddball
Access things this week that Access 2003 SP2, under XP Pro with all
updates,
is kind of buggy. Were earlier releases more stable?

Thanks,
George

Allen, finally traced the problem to what is apparently a known problem
area - linked Excel spreadsheets. Turns out the link wizard was forcing a
Number data type, which really screwed up the works because the column in
this case has empty fields. What was really nutty was that the append query
would work a first time, then never again until you do something like delete
a query field, then add the same field back, and it would work again one
time, then break again.

Further, discovered in trying to cause the link wizard to type-cast that
column as text - if you tell it that the first row is not a header row, and
have text in the first cell of the column, then Access correctly type-casts
it as text. However, if you tell it that the first row is a header row,
then it starts checking fields on the THIRD row rather than the second -
doesn't matter what you put in the second cell, it's ignored. A really
stupid programming error, and it's pretty remarkable that it hasn't been
corrected. Also came across a Microsoft.com help page with, among other
things, an Excel macro that was supposed to help remedy the problem as a
work-around by putting a space preamble to selected fields and then removing
it, and it didn't make any difference. Imagine all the time wasted by
unfortunate users, and the effort that went into investigating it and
creating a special page - why don't they just fix the darn thing!!

I appreciated your pointing me to your Web site - the guidance is
interesting.

Regards,
George


"Allen Browne" wrote in message
...
An overflow indicates that a numeric value is outside the range of the
field
Access is trying to append it to.

If the source query has calculated fields, you may need to typecast the
calculation with CDbl(), CCur(), CLng(), etc. You will probably need to
use
Nz() inside that also, to avoid problems with Null.

Another possibility is that the field size in the target table is not
adequate. For example, a Number field of size Integer will not accept
values
larger than 32767.

If the field sizes are not an issue, and typecasting does not solve the
problem, it is possible that Access is confused about what the query is
referrring to. Try unchecking the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
Info on why:
http://allenbrowne.com/bug-03.html

A2003 is not less stable than previous versions. The query engine (JET 4)
is
actually the same for Access 2000, 2002, and 2003. This engine is somewhat
less efficient (for example with user-defined function calls) and less
capable of recognising data types (on calculated fields) than its
predecessor (JET 3.5 in Access 97), but it is not less stable.

It is possible to configure A2003 so it is arguably the most stable
version
of Access, at least since version 2. For info on how to do that, see:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html

--
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.

"G Lykos" wrote in message
...
Am regularly running into a problem where something snaps and an append
query stops running with the captioned error message. I'm not doing any
math calculations, just appending selected fields from a filtered record
set.

Once this happens, it stays broken until I edit the query (as in add a
field, then delete it, functionally taking me back to when I started)
and
then the query works again for a while. It's driving me nuts because I
don't know how to identify the source, and I'm tired of having to futs
around with nonsense edits to the query to get it working again. Any
ideas
what might be causing this??

I have the impression after having struggled with this and other oddball
Access things this week that Access 2003 SP2, under XP Pro with all
updates,
is kind of buggy. Were earlier releases more stable?

Thanks,
George

Excellent. Solved.

I understand that the Access Development team at Microsoft has been working
on improving the import from Excel next version (A2007.)

Access has always had such problems interpreting import data correctly that
my standard way of programming a user-level import into a temp table where
all fields are text. Then query the temp table to ensure the data in every
field can be interpreted correctly, that it typecasts reliably, that there
are no values that will fail because they don't match a related table or are
not unique, etc, etc. Only after the user fixes the problems in the temp
table do we allow the data into the true target table.

--
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.

"G Lykos" wrote in message
...
Allen, finally traced the problem to what is apparently a known problem
area - linked Excel spreadsheets. Turns out the link wizard was forcing a
Number data type, which really screwed up the works because the column in
this case has empty fields. What was really nutty was that the append
query
would work a first time, then never again until you do something like
delete
a query field, then add the same field back, and it would work again one
time, then break again.

Further, discovered in trying to cause the link wizard to type-cast that
column as text - if you tell it that the first row is not a header row,
and
have text in the first cell of the column, then Access correctly
type-casts
it as text. However, if you tell it that the first row is a header row,
then it starts checking fields on the THIRD row rather than the second -
doesn't matter what you put in the second cell, it's ignored. A really
stupid programming error, and it's pretty remarkable that it hasn't been
corrected. Also came across a Microsoft.com help page with, among other
things, an Excel macro that was supposed to help remedy the problem as a
work-around by putting a space preamble to selected fields and then
removing
it, and it didn't make any difference. Imagine all the time wasted by
unfortunate users, and the effort that went into investigating it and
creating a special page - why don't they just fix the darn thing!!

I appreciated your pointing me to your Web site - the guidance is
interesting.

Regards,
George


"Allen Browne" wrote in message
...
An overflow indicates that a numeric value is outside the range of the
field
Access is trying to append it to.

If the source query has calculated fields, you may need to typecast the
calculation with CDbl(), CCur(), CLng(), etc. You will probably need to
use
Nz() inside that also, to avoid problems with Null.

Another possibility is that the field size in the target table is not
adequate. For example, a Number field of size Integer will not accept
values
larger than 32767.

If the field sizes are not an issue, and typecasting does not solve the
problem, it is possible that Access is confused about what the query is
referrring to. Try unchecking the Name AutoCorrect boxes under:
Tools | Options | General
Then compact the database:
Tools | Database Utilities | Compact
Info on why:
http://allenbrowne.com/bug-03.html

A2003 is not less stable than previous versions. The query engine (JET 4)
is
actually the same for Access 2000, 2002, and 2003. This engine is
somewhat
less efficient (for example with user-defined function calls) and less
capable of recognising data types (on calculated fields) than its
predecessor (JET 3.5 in Access 97), but it is not less stable.

It is possible to configure A2003 so it is arguably the most stable
version
of Access, at least since version 2. For info on how to do that, see:
Preventing corruption
at:
http://allenbrowne.com/ser-25.html

"G Lykos" wrote in message
...
Am regularly running into a problem where something snaps and an append
query stops running with the captioned error message. I'm not doing
any
math calculations, just appending selected fields from a filtered
record
set.

Once this happens, it stays broken until I edit the query (as in add a
field, then delete it, functionally taking me back to when I started)
and
then the query works again for a while. It's driving me nuts because I
don't know how to identify the source, and I'm tired of having to futs
around with nonsense edits to the query to get it working again. Any
ideas
what might be causing this??

I have the impression after having struggled with this and other
oddball
Access things this week that Access 2003 SP2, under XP Pro with all
updates,
is kind of buggy. Were earlier releases more stable?