iff issue with Like "*" (1 Viewer)

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi Guys, new here, thanks for any help offered.
I'm self taught with Access so have limited knowledge and have hit an issue I can't find an answer to.

If I use Like "*" in the criteria for a ShortText field, I get the results I expect.

However if I use the following SQL I get no results

IIf(3=3,(([SimpleTable].[ShortText]) Like "*"),(([SimpleTable].[ShortText]) Like "*"))

could someone explain what's going wrong?
 

JHB

Have been here a while
Local time
Today, 12:49
Joined
Jun 17, 2012
Messages
7,732
Post the whole SQL-string.
 

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
hi, thanks for looking, whole string:-

SELECT SimpleTable.ShortText
FROM SimpleTable
WHERE (((SimpleTable.ShortText)=IIf(3=3,(([SimpleTable].[ShortText]) Like "*"),(([SimpleTable].[ShortText]) Like "*"))));
 

JHB

Have been here a while
Local time
Today, 12:49
Joined
Jun 17, 2012
Messages
7,732
The below is the correct way:
Code:
SELECT SimpleTable.ShortText
FROM SimpleTable
WHERE (("3"="3" And (SimpleTable.ShortText) Like "*")) OR (((SimpleTable.ShortText) Like "*"));
Or this:
Code:
SELECT SimpleTable.ShortText
FROM SimpleTable
WHERE IIf(3=3,(([SimpleTable].[ShortText]) Like "*"),(([SimpleTable].[ShortText]) Like "*"));
 

isladogs

MVP / VIP
Local time
Today, 11:49
Joined
Jan 14, 2017
Messages
18,186
Congratulations.
That is the most convoluted and pointless WHERE filter I've seen in a long time
The IIf statement has three parts: IIF(Condition to check, result if true, result if false)

Your true and false parts are identical.
Your condition 3=3 is always true
So you don't need an IIF at all

Code:
SELECT SimpleTable.ShortText
FROM SimpleTable
WHERE SimpleTable.ShortText Like "*";

But this just selects all values for ShortText so all you need is

Code:
SELECT SimpleTable.ShortText
FROM SimpleTable

BTW I do hope that these aren't real table/field names
 

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi isladogs,thanks for the feedback. The SQL I posted was the simplest way I could recreate the strange result I was getting.

The actual SQL is going to be something like this

SELECT SimpleTable.ShortText
FROM SimpleTable
WHERE IIf(ConditionIwillTest,(([SimpleTable].[ShortText]) Like "EN" & "*"),(([SimpleTable].[ShortText]) Like "BU" & "*"));

hope that helps
 

isladogs

MVP / VIP
Local time
Today, 11:49
Joined
Jan 14, 2017
Messages
18,186
It rarely helps to try and simplify the code used and I think this proves that point.

Perhaps JHB has already given you the info you need. If not, we need more info
I've no idea what your condition is or what results you are getting
Why don't you post exactly what your SQL is together with the actual results.
Or better still post a cut down version of your database with the table containing enough results to test and the actual query/SQL you are using
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:49
Joined
Sep 21, 2011
Messages
14,038
What is wrong with just using "EN*" or "BU*" ?
 

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
hi guys, no it's still not working. I'll get a sample of what I'm trying to do.
It's basically filtering a query depending on whats in some text boxes on a form
 

JHB

Have been here a while
Local time
Today, 12:49
Joined
Jun 17, 2012
Messages
7,732
As isladogs mention, post your database with some sample data, zip it because you haven't post 10 post yet.
 

June7

AWF VIP
Local time
Today, 03:49
Joined
Mar 9, 2014
Messages
5,423
Possibly:

SELECT [ShortText]
FROM SimpleTable
WHERE [ShortText] LIKE IIf(ConditionIwillTest, "EN", "BU") & "*";

or

SELECT [ShortText]
FROM SimpleTable
WHERE [ShortText] LIKE IIf(ConditionIwillTest, "EN*", "BU*");

Both work.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 07:49
Joined
Jan 23, 2006
Messages
15,361
GDX,
It's always a good idea to set some context for your post by describing to readers WHAT you are trying to do and then show some of your code/sql etc.

11 posts in and it seems (to me at least) people are still guessing at what you are trying to do.
Good luck with your project.
 

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi,I've attached the DB

If you open the Form you will see 3 listboxes, the top two are getting filtered by the text in the 3 Textboxes, the bottom one is basically the raw table the data is coming from

[Simple Filter] shows the qPartsSimpleFilter query but if you look at the 3rd column you will see there are no Null entries

[Null Filter] shows the filter working and showing the null values but if you filter on misc then you will still get the nulls as well as your filter target.

What I was hoping to do with the IIf was determine if there was an entry in the Textbox, then filter using that text but if there wasn't any text in the Textbox then don't filter that field.

TLDR: some fields have null entries whats the best way to filter but still see nulls when they aren't filtered
 

Attachments

  • FilterTest.zip
    46.5 KB · Views: 29

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi jdraw, I wasn't sure of the best way to explain the issue but the basic question is
Why is Like "*" valid in criteria but invalid when inside an iif statement
 

plog

Banishment Pending
Local time
Today, 06:49
Joined
May 11, 2011
Messages
11,611
Why is Like "*" valid in criteria but invalid when inside an iif statement

"LIKE" is valid inside an Iif statement. Anything that you can get to resolve to true or false is. Your issues lies in the way you are generating SQL.

WHERE IIf(3=3,(([SimpleTable].[ShortText]) Like "*"),(([SimpleTable].[ShortText]) Like "*"));

Short answer: You can't dynamically write SQL within the SQL itself. The SQL engine expects SQL--> it can understand logic that relates to the data it operates on, but not on logic that determines what the SQL statement itself is.
 

JHB

Have been here a while
Local time
Today, 12:49
Joined
Jun 17, 2012
Messages
7,732
Is that what you want, look at the [Null Filter] list.
 

Attachments

  • FilterTest.accdb
    484 KB · Views: 30

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi JHB, yes, that was the end result I was hoping to get, thank you

Hypothetically if there were another 10 fields which may have null values I would need to do that for each one :D
 

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi Guys,
thought I would finish this thread with what I've found out was going wrong.

I don't know if this is obvious to seasoned users but it certainly caught me out.

Basically if you create an IIF function within the Criteria Field (see pic) then Access will generate SQL which wont work. Like this

SELECT TestTable.textdata
FROM TestTable
WHERE (((TestTable.textdata)=IIf(False,([TestTable].[textdata]) Like "fr*",([TestTable].[textdata]) Like "we*")));

if you edit the SQL so that it's like this

SELECT TestTable.textdata
FROM TestTable
WHERE IIf(False,([TestTable].[textdata]) Like "fr*",([TestTable].[textdata]) Like "we*");

then it should work
 

Attachments

  • BuildIIFinCriteria.jpg
    BuildIIFinCriteria.jpg
    51.9 KB · Views: 36

GDX

New member
Local time
Today, 11:49
Joined
Jan 14, 2019
Messages
9
Hi June7,
sorry I didn't see it, I wish I had, certainly a cleaner way to fix the SQL Access makes
 

Users who are viewing this thread

Top Bottom