Solved

Problem with IIf code

Posted on 2015-02-13
5
110 Views
Last Modified: 2016-02-11
What is wrong with:

=IIF(isnull(DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])),"",=DLookUp("[MeterMake]","tblMeterTypes","[ID] = " & [Reports]![rptMeterCertification]![txtMeterMake])
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40608803
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
 
LVL 26

Expert Comment

by:Nick67
ID: 40609197
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40609311
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
 
LVL 84
ID: 40609711
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
 

Author Closing Comment

by:SteveL13
ID: 40609776
I created a query per your suggestion.  Thank you.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question