Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 117
  • Last Modified:

Problem with IIf code

What is wrong with:

=IIF(isnull(DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])),"",=DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])
0
SteveL13
Asked:
SteveL13
1 Solution
 
PatHartmanCommented:
You have an extra = sign

A better solution would be to use a query as the recordsource for the report that joins the main table to tblMeterTypes using a Left Join.  Then you could bind the control to a field in the recordsource and not have to run TWO queries for each row in your report.  Access is going to run the query once to check for null and then again if the IIf() determined that the query returned a value.

In general, avoid DLookup()s and other domain functions whenever you have an alternative and in this case, you have one, so use it.  The join is significantly more efficient than running multiple queries for each row.  If your report produced only 400 rows, your IIf() would require running 800 queries!!!!
0
 
Nick67Commented:
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?
0
 
IrogSintaCommented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
SteveL13Author Commented:
I created a query per your suggestion.  Thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now