Peculiar Problem with Controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report which utilises various calculations mostly done in the
underlying SQL statement. However in the report I need to divide all results
by another field. Most work OK but 2 of the controls return #error. All use
the same calculation i.e. =[field name] / [Euro Conversion Rate]

The peculiar thing is that if I strip out the last part of the calculation
and just set the control source to =[field name] it still returns #error yet
this works OK with other controls. I can't see anything different with this
field, any hints as to possible causes would be much appreciated.
 
Several possibilities:

1. Conflict with Name.
If the Name of a control is the same as the name of one of the fields, but
it is bound to an expression, Access gets confused.

To solve this problem, rename the control.


2. Division by zero.
If there is any possibilty that [Euro Conversion Rate] could be zero, that
would generate the error.

To solve this problem, try:
=IIf([Euro Conversion Rate] = 0, 0, [field name] / [Euro Conversion
Rate])


3. Data type misunderstood.
If either [field name] or [Euro Conversion Rate] is a calculated query field
(not a field stored in the table), then Access may not understand the data
type correctly. It then fails when it tries to divide text by a number (or
vice versa). If you open the query directly, you may even see the field
left-aligning like text instead of right-aligning like a number.

To solve this problem, typecast the calculated query field. Details on this
technique in this article:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html
 
Thanks Allen, it was the name conflict causing the problem, now if you can
help with my other post re totals I might get to go home tonight!! Thanks
again

Allen Browne said:
Several possibilities:

1. Conflict with Name.
If the Name of a control is the same as the name of one of the fields, but
it is bound to an expression, Access gets confused.

To solve this problem, rename the control.


2. Division by zero.
If there is any possibilty that [Euro Conversion Rate] could be zero, that
would generate the error.

To solve this problem, try:
=IIf([Euro Conversion Rate] = 0, 0, [field name] / [Euro Conversion
Rate])


3. Data type misunderstood.
If either [field name] or [Euro Conversion Rate] is a calculated query field
(not a field stored in the table), then Access may not understand the data
type correctly. It then fails when it tries to divide text by a number (or
vice versa). If you open the query directly, you may even see the field
left-aligning like text instead of right-aligning like a number.

To solve this problem, typecast the calculated query field. Details on this
technique in this article:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

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

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

Sheila D said:
I have a report which utilises various calculations mostly done in the
underlying SQL statement. However in the report I need to divide all
results
by another field. Most work OK but 2 of the controls return #error. All
use
the same calculation i.e. =[field name] / [Euro Conversion Rate]

The peculiar thing is that if I strip out the last part of the calculation
and just set the control source to =[field name] it still returns #error
yet
this works OK with other controls. I can't see anything different with
this
field, any hints as to possible causes would be much appreciated.
Click to expand...
Click to expand...
 
Back
Top