Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Problem with IIf code

What is wrong with:

=IIF(isnull(DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])),"",=DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just about everything is wrong with it.
The extra equals sign @PatHartman noted is a start
A pair of DLookup()s in a ControlSource on a report.
DLookup is virtually always a bad idea.
IF, and that's a big IF, [ID] is numeric, then this poorly performing dog may work.
If [ID] is text then you'll need it surrounded in quotes.
It is the very definition of creating unmaintainable code.

And

It doesn't make much sense from a logical perspective
You are doing a DLookup:
DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])
to get a Value for MeterMake, checking to see if it is null.
If it is, then you're sending in ""
If it isn't, you are doing that same expensive operation again.
I know you like to avoid VBA code, but this really can't run well.

Do all this stuff in the report's RecordSource query.
You should have a column in the query like
TheMeterMake:IIf(isNull([MeterMake])=true,"",[MeterMake])
In the query, tblMeterTypes should be in there with the appropriate joins so that [MeterMake] is available.

It's one thing to avoid adding tables and joins to a query powering a form that needs to remain editable.  Even then, DLookup should be avoided for controls in a detail section of a continuous form.  On a single form, ok, maybe.

But a report doesn't need to be editable.  Add tables and joins as required to get all the fields in that you need -- and if you need to not show zeros and nulls but empty string "" instead, then use IIf in the query to get that done.

And I am sure that @PatHartman would agree, step back and look at why NULL is even possible in the field.  Should a default value have been assigned to the field and NULL disallowed right from the get-go?
I agree with the previous experts.  Your recordsource should indeed be a query joining both tables (a very simple query make).  I want to add that you could use the Nz function in place of IsNull.  If MeterMake returns a Null value, then all you need to return a zero length string is Nz(MeterMake).

Ron
You're generally much better off not using IIF and DLookups in queries, if it can be avoided (and it almost always can).

You should be able to include tblMeterMake in the query with a LEFT JOIN. Using a LEFT JOIN would join that table to rows where a related record exists in tblMeterMake, and in rows where no related record exists you'd have a NULL value. You can handle that in the report directly.

Something like:

SELECT * FROM SomeTable LEFT OUTER JOIN tblMeterMake ON SomeTable.ID=tblMeterMake.TheRelatedField etc etc

If you need to show the "second" part of the DLOOKUP, then include the other table as well:

SELECT * FROM SomeTable LEFT OUTER JOIN tblMeterMake ON SomeTable.ID=tblMeterMake.TheRelatedField LEFT OUTER JOIN tblMeterTypes ON SomeTable.ID=tblMeterTypes.TheRelatedField etc etc

For both, you'd end up with (a) ALL records from SomeTable and then (b) related records from the LEFT JOIN table. You can then take action based on what's returned by those LEFT JOINs.
Avatar of SteveL13

ASKER

I created a query per your suggestion.  Thank you.