UtterAccess.com
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    
 
   
Scot
post Sep 4 2019, 11:35 AM
Post#1



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.
CODE
SELECT TBL_PLD.PLDName
FROM TBL_PLD
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.

TIA.

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


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
 
theDBguy
post Sep 4 2019, 11:51 AM
Post#3


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?
CODE
...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
 
arnelgp
post Sep 4 2019, 12:29 PM
Post#4



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


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

or

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