#Error in Expression

  • Thread starter Thread starter Gsurfdude
  • Start date Start date
G

Gsurfdude

Hello,

On an report, I get an "#Error" in the text box control who's Control Source
is set to this expression.
=Sum(IIf([qry_449_Report]![ROUTE_TYPE]=1 And
([qry_449_Report]![DIV_HWY_SURF_TYPE]='G' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='G' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='H' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='H' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='I' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='I'),[qry_449_Report]![SECTION_LENGTH],0))

But, I do not get this error with this one (identical to above except
[qry_449_Report]![DIV_HWY_SURF_TYPE]='I' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='I') is not in the expression E.G.

This works...

=Sum(IIf([qry_449_Report]![ROUTE_TYPE]=1 And
([qry_449_Report]![DIV_HWY_SURF_TYPE]='G' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='G' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='H' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='H'),[qry_449_Report]![SECTION_LENGTH],0))

I am a little stumped why even tho syntax looks OK in the one I get the error
 
Suggestions:

1. You don't need the query name in the expression (unless the field name is
ambiguous.)

2. The IN operator might be easier than repeating the ORs.

Try:
=Sum(IIf([ROUTE_TYPE]=1 AND
([DIV_HWY_SURF_TYPE] IN ('G','H','I') OR
[UDIV_HWY_SURFACE_TYPE] IN ('G','H','I'))
[SECTION_LENGTH], 0))

This assumes that:
a) Route_Type and Section_Length are Number type fields;
b) the DIV... and UDIV fields are Text type.
c) You have text boxes on your report for all 4 fields (even if hidden.)
 
Worked! Thank you.

Allen Browne said:
Suggestions:

1. You don't need the query name in the expression (unless the field name is
ambiguous.)

2. The IN operator might be easier than repeating the ORs.

Try:
=Sum(IIf([ROUTE_TYPE]=1 AND
([DIV_HWY_SURF_TYPE] IN ('G','H','I') OR
[UDIV_HWY_SURFACE_TYPE] IN ('G','H','I'))
[SECTION_LENGTH], 0))

This assumes that:
a) Route_Type and Section_Length are Number type fields;
b) the DIV... and UDIV fields are Text type.
c) You have text boxes on your report for all 4 fields (even if hidden.)

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

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


Gsurfdude said:
Hello,

On an report, I get an "#Error" in the text box control who's Control
Source
is set to this expression.
=Sum(IIf([qry_449_Report]![ROUTE_TYPE]=1 And
([qry_449_Report]![DIV_HWY_SURF_TYPE]='G' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='G' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='H' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='H' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='I' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='I'),[qry_449_Report]![SECTION_LENGTH],0))

But, I do not get this error with this one (identical to above except
[qry_449_Report]![DIV_HWY_SURF_TYPE]='I' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='I') is not in the expression
E.G.

This works...

=Sum(IIf([qry_449_Report]![ROUTE_TYPE]=1 And
([qry_449_Report]![DIV_HWY_SURF_TYPE]='G' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='G' Or
[qry_449_Report]![DIV_HWY_SURF_TYPE]='H' Or
[qry_449_Report]![UDIV_HWY_SURFACE_TYPE]='H'),[qry_449_Report]![SECTION_LENGTH],0))

I am a little stumped why even tho syntax looks OK in the one I get the
error
Click to expand...
Click to expand...
 
Back
Top