numeric field overflow

[Peter Kopke]

I have a form with totals fields in the footer that take the sum of
fields in the form:

The control source of one of these totals fields is

=Sum([gross_amt])

When the form is run I get the following error message:

"The decimal field's precision is too small to accept the numeric
you attempted to add".

The help files says to change the Field Size property.
However there is no Field Size property when I right-click on the field
and select properties.

Where can I find the Field Size proerty, or what other method
is there for fixing this.

Thanks,
Peter Kopke
(e-mail address removed)

 

[ruralguy via AccessMonster.com]

You need to look at your tables and possibly change some Single fields to
Double.

I have a form with totals fields in the footer that take the sum of
fields in the form:

The control source of one of these totals fields is

=Sum([gross_amt])

When the form is run I get the following error message:

"The decimal field's precision is too small to accept the numeric
you attempted to add".

The help files says to change the Field Size property.
However there is no Field Size property when I right-click on the field
and select properties.

Where can I find the Field Size proerty, or what other method
is there for fixing this.

Thanks,
Peter Kopke
(e-mail address removed)

 

[Peter Kopke]

The labels are linked to a MySQL database through ODBC.
Where can I change this setting?

Peter

You need to look at your tables and possibly change some Single fields to
Double.

I have a form with totals fields in the footer that take the sum of
fields in the form:

The control source of one of these totals fields is

=Sum([gross_amt])

When the form is run I get the following error message:

"The decimal field's precision is too small to accept the numeric
you attempted to add".

The help files says to change the Field Size property.
However there is no Field Size property when I right-click on the field
and select properties.

Where can I find the Field Size proerty, or what other method
is there for fixing this.

Thanks,
Peter Kopke
(e-mail address removed)

 

[John Vinson]

The labels are linked to a MySQL database through ODBC.
Where can I change this setting?

In MySQL. Access can't do it, AFAIK.

John W. Vinson[MVP]

 

[Peter Kopke]

This is a calculated field on a form. It should have
no bearing where the data is coming from. How do
I set the precision of the calculated field?

Peter Kopke

 

[John Vinson]

This is a calculated field on a form. It should have
no bearing where the data is coming from. How do
I set the precision of the calculated field?

AH! Mistunderstood.

Try

The control source of one of these totals fields is

=Sum(CDbl([gross_amt]))

or

=Sum(CCur([gross_amt]))

CDbl will give you a range up to 10^308 and 14 decimals accuracy; CCur
will give you a range into the trillions and exactly 4 decimals.

John W. Vinson[MVP]

 

[Peter Kopke]

That makes sense, but I still get the same error. I used Ccur for the
currency totals and CInt for the integer totals. It seems that while I am
increasing the storage for the items I am adding up, I am not increasing
the storage for the sum. Is there a way to tell the total field to use a
larger domain?

Regards,
Peter Kopke
(e-mail address removed)

This is a calculated field on a form. It should have
no bearing where the data is coming from. How do
I set the precision of the calculated field?

AH! Mistunderstood.

Try

The control source of one of these totals fields is

=Sum(CDbl([gross_amt]))

or

=Sum(CCur([gross_amt]))

CDbl will give you a range up to 10^308 and 14 decimals accuracy; CCur
will give you a range into the trillions and exactly 4 decimals.

John W. Vinson[MVP]

 

[John Vinson]

That makes sense, but I still get the same error. I used Ccur for the
currency totals and CInt for the integer totals. It seems that while I am
increasing the storage for the items I am adding up, I am not increasing
the storage for the sum. Is there a way to tell the total field to use a
larger domain?

I'd use CLng (range to two billion odd) rather than CInt (range to
65535) - but no, I don't see why doing a Sum on a Currency field
should give anything other than a Currency result. I wonder if the
error is somewhere else? Could you post the SQL of your query?

John W. Vinson[MVP]

 

[Peter Kopke]

I changed to CLng. I also changed to CCur(sum(CCur([gross_amt])))
in order to try to force the totals field to use more storage.

There is no query involved, it is just
a tabular form that lists every column of a table linked to
a MySQL table. In the footer I have sum totals for five fields:

two fields with MySQL type decimal(9,2)
one field with MySQL type decimal(6,2)
two fields with MysQL type smallint(6)

The largest sum here is about 12 million. There are about 1100
rows in the table.

When the form first loads the all totals fields show 0. After a few seconds
they all change to #Error. When I scroll down through the data,
eventually I get the error message. The error message doesn't actually say
which field is involved.

BTW, this behavior is new. The totals used to display correctly when there
were fewer rows in the table.

Regards,
Peter Kopke
(e-mail address removed)

 

[Peter Kopke]

You have solved my problem, thank you.
I recreated the MySQL table as decimal (12,2),
relinked the table in Access, and the form runs
without errors now.


Thanks again,
Peter Kopke