#Type! error in calculated field on Access Report

I'm at a loss with this one... it's probably something stupid but I cannot figure it out.

I'm getting a #Type! error in a calculated field in an Access report, but it does not affect all the records in the report, and it only appears when the report is in Normal view.  The field is fine in Preview.  Further, depending on how fast I scroll the report, the error appears at different records.  Below is the code for the field.

=IIf([qty]=0,0,Round([qty]/[Reports]![rpt_net_item_mix]![qry_mix_view_net_order_count_subreport].[Report]![tot_orders],4))

I'm not sure what other information might be useful.
mriozzo68Asked:
Who is Participating?
 
mriozzo68Connect With a Mentor Author Commented:
it only has one record.

I fixed it by removing the subreport from the header, opening the subreport hidden, and calling the [tot_orders] value into a hidden field in the line record of the report and calculating from there.
0
 
Pawan KumarDatabase ExpertCommented:
Check is this working...

=IIf([qty]=0,0,Round([Reports]![rpt_net_item_mix]![qry_mix_view_net_order_count_subreport].[Report]![tot_orders],4))

Then also try ..

=IIf([qty]=0,0,Round(([qty] * 1.0)/[Reports]![rpt_net_item_mix]![qry_mix_view_net_order_count_subreport].[Report]![tot_orders],4))
0
 
mriozzo68Author Commented:
same error.  The field calculates correctly, in Print Preview it's fine.  It's normal view that is throwing the error.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
and this ..

=IIf([qty]=0,0, [Reports]![rpt_net_item_mix]![qry_mix_view_net_order_count_subreport].[Report]![tot_orders])

If yes then this has some problem --> [Reports]![rpt_net_item_mix]![qry_mix_view_net_order_count_subreport].[Report]![tot_orders]
0
 
mriozzo68Author Commented:
This throws a #Error message.  It seems to be when the total_orders field (which is in a subform on the header) leaves the screen?
0
 
PatHartmanCommented:
Does your subreport have more than ONE record.  If it does, exactly which record do you think your expression is referring to?
0
 
PatHartmanCommented:
Why would you use a subreport at all?  Why not simply use a domain function to get the total?
0
 
mriozzo68Author Commented:
Inexperience?
0
 
PatHartmanCommented:
Good answer :)
0
 
mriozzo68Author Commented:
I know my limitations.  I'm sure a real dba would pull their hair out but the roots if they got a hold of one of my dbases, but as inelegant as they might be they've been running for years with minimal issues.
0
 
Pawan KumarDatabase ExpertCommented:
could you pls post the solution?
0
 
mriozzo68Author Commented:
I fixed it by removing the subreport from the header, opening the subreport hidden, and calling the [tot_orders] value into a hidden field in the line record of the report and calculating from there.
1
 
mriozzo68Author Commented:
it worked
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.