iif statement returning 1 on upper spec

[Miranda]

I have the following formula in a query. The upper spec of the dlookup is
returning a 1 when it shouldn't be. Example: mindiam 10.000 maxdiamb
11.000. When 11.000 is entered, it shows as out of spec. Any help will be
greatly appreciated!

OutsideAverageDiameterA: IIf([Average Diameter
A]<DLookUp("[MinDiam]","[tblProductSpecs]","[product]='" & Data!Product &
"'") Or [Average Diameter
A]>DLookUp("[MaxDiam]","[tblProductSpecs]","[product]='" & Data!Product &
"'"),1,0)

 

[Klatuu]

The logic of the code is straight forward. There are two possibilites I
would look for.
1.
Is [Average Diameter A] exactly 11.000
Is [MaxDiam] exactly 11.000
In other words, could there be some rounding going on that is hiddin a
fractional
difference?

2.
Your DLookups are not protected against a Null value. If a DLookup does
not find a
match in the criteria, it will return Null.

One other point to consider (Although it has nothing to do with ther
problem). Domain Aggregate Functions in querys make the queries very slow.
Since you are dealing with a table where you can do a DLookup, most likely
you can join the tblProductSpecs table in your query on [product] and do the
calculation in the query the query itself. Change the calculated field:
OutsideAverageDiameterA: [Average Diameter A] < [MinDiam] Or [Average
Diameter A] > [MaxDiam]

If the field OutsideAverageDiameterA returns True (-1) and if not False (0)

It will dramatically reduce the time the query takes to run.

 

[Miranda]

I have re-entered all numbers to ensure that there is no rounding and have
corrected any null values. One other thing that may be helpfull. When I run
my query, the Average Diameter A (and B) are aligned on to the left like
text. I have checked my tables and they are all number formats. Could this
be causing the problem?
--
Miranda

The logic of the code is straight forward. There are two possibilites I
would look for.
1.
Is [Average Diameter A] exactly 11.000
Is [MaxDiam] exactly 11.000
In other words, could there be some rounding going on that is hiddin a
fractional
difference?

2.
Your DLookups are not protected against a Null value. If a DLookup does
not find a
match in the criteria, it will return Null.

One other point to consider (Although it has nothing to do with ther
problem). Domain Aggregate Functions in querys make the queries very slow.
Since you are dealing with a table where you can do a DLookup, most likely
you can join the tblProductSpecs table in your query on [product] and do the
calculation in the query the query itself. Change the calculated field:
OutsideAverageDiameterA: [Average Diameter A] < [MinDiam] Or [Average
Diameter A] > [MaxDiam]

If the field OutsideAverageDiameterA returns True (-1) and if not False (0)

It will dramatically reduce the time the query takes to run.

I have the following formula in a query. The upper spec of the dlookup is
returning a 1 when it shouldn't be. Example: mindiam 10.000 maxdiamb
11.000. When 11.000 is entered, it shows as out of spec. Any help will be
greatly appreciated!

OutsideAverageDiameterA: IIf([Average Diameter
A]<DLookUp("[MinDiam]","[tblProductSpecs]","[product]='" & Data!Product &
"'") Or [Average Diameter
A]>DLookUp("[MaxDiam]","[tblProductSpecs]","[product]='" & Data!Product &
"'"),1,0)