X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Using Like "*" As Truepart Of Iif(isnull, Access 2016    
post Sep 4 2019, 11:35 AM

Posts: 819
Joined: 24-November 08
From: Silver Spring Maryland, USA

Okay, I feel like I should know the answer to this, but it turns out that in the past I just dealt with these scenarios in VBA code that opened a report. Never with a query before.

So, I've a query is a field that has a form Combo box. If the combo box is blank though, I'd like it to produce all records. Here's my code.
WHERE (((TBL_PLD.PLDName)=IIf(IsNull([Forms]![FRM_Reports]![Cbo_PLDSelect]),([TBL_PLD].[PLDName]) Like "*",[Forms]![FRM_Reports]![Cbo_PLDSelect])));

I've played with NZ and different truepart statements but the results are always Nothing, rather than everything.


- Scot
Go to the top of the page
post Sep 4 2019, 11:48 AM

UA Admin
Posts: 35,841
Joined: 20-June 02
From: Newcastle, WA

Perhaps there's a clue here: "...If the combo box is blank ..."

Blank really isn't so useful when talking about values in the context of an Access Relational Database Application. That's because "blank" could be the interpretation for a Null or for a Zero Length String. Your syntax tests for the former, but not the latter.

In other words, if we assume that a "blank" looking combo box is Null, and don't account for the possibility that it's actually not null, but a Zero Length String, then we might not get results we want.

Please consider whether your combo box actually contains the values you expect.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Sep 4 2019, 11:51 AM

Access Wiki and Forums Moderator
Posts: 76,375
Joined: 19-June 07
From: SunnySandyEggo

Hi Scot. There's an easier solution than an IIf() statement. How about trying it this way?
...WHERE PLDName=[Forms]![FRM_Reports]![Cbo_PLDSelect] OR [Forms]![FRM_Reports]![Cbo_PLDSelect] Is Null

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Sep 4 2019, 12:29 PM

Posts: 1,419
Joined: 2-April 09
From: somewhere out there...

WHERE TBL_PLD.PLDName Like Nz([Forms]![FRM_Reports]![Cbo_PLDSelect],  "*");


WHERE TBL_PLD.PLDName = IIF(Trim([Forms]![FRM_Reports]![Cbo_PLDSelect] & "")="", TBL_PLD.PLDName, [Forms]![FRM_Reports]![Cbo_PLDSelect]);

Never stop learning, because life never stops teaching.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    13th October 2019 - 08:21 PM