Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with IIf code

Posted on 2015-02-13
5
Medium Priority
?
115 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 39

Accepted Solution

by:
PatHartman earned 2000 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 85
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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

664 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