Solved

Problem with IIf code

Posted on 2015-02-13
5
107 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
5 Comments
 
LVL 34

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now