Using IIF in queries (1 Viewer)

GreyArea · Oct 15, 2019

I've a very simple database of songs that I would like users to be able to enter text to search for, with an option to search for "starts with" the text or "contains" the text. I've been trying to use the "IIF" statement in criteria, but I cannot get it to work.

I've got a toggle button whose caption changes from "starts with" when false (unclicked), to "contains" when true (clicked). The IIF statement I am trying to use is as follows;

Code:

IIf([Forms]![SongSearch]![Tog_Song].[Caption]="starts with",[Forms]![SongSearch]![TB_SongFilter] & "*","*" & [Forms]![SongSearch]![TB_SongFilter] & "*")

Simply put if the button is unpressed I want the criteria to search for "*songtitle" and if pressed to search for "*songtitle*".

If I put the IIf statement above into a field, and then show the field on the form it shows I'm building the filter criteria correctly. But if I enter it into the criteria then no records are returned.

I thought this would be fairly simple...what am I doing wrong?

theDBguy · Oct 15, 2019

Hi. Welcome to AWF! Are you using the Like operator? Just making sure...

GreyArea · Oct 15, 2019

Yes, "Like" appears when I put this into the query...but no results returned

theDBguy · Oct 15, 2019

GreyArea said:
Yes, "Like" appears when I put this into the query...but no results returned

Can you post the complete SQL statement for the query?

GreyArea · Oct 15, 2019

I was surprised to find that I could (quite new at this!);

Code:

SELECT Songs.SONG, Songs.ARTIST
FROM Songs
WHERE (((Songs.SONG)=IIf([Forms]![SongSearch]![Tog_Song].[Caption]="starts with",(Songs.SONG) Like [Forms]![SongSearch]![TB_SongFilter] & "*",(Songs.SONG) Like "*" & [Forms]![SongSearch]![TB_SongFilter] & "*")))
ORDER BY Songs.SONG, Songs.ARTIST;

I'm thinking it's probably that the WHERE doesn't like the IIF statement...but I don't know how to fix it.

theDBguy · Oct 15, 2019

GreyArea said:
I was surprised to find that I could (quite new at this!);

Code:

SELECT Songs.SONG, Songs.ARTIST FROM Songs WHERE (((Songs.SONG)=IIf([Forms]![SongSearch]![Tog_Song].[Caption]="starts with",(Songs.SONG) Like [Forms]![SongSearch]![TB_SongFilter] & "*",(Songs.SONG) Like "*" & [Forms]![SongSearch]![TB_SongFilter] & "*"))) ORDER BY Songs.SONG, Songs.ARTIST;

I'm thinking it's probably that the WHERE doesn't like the IIF statement...but I don't know how to fix it.

Hi. Thanks. Take out the equal sign and replace it with Like.

GreyArea · Oct 15, 2019

I fixed it a different way...this works perfectly...but the builder doesn't like it...tryingto open in "design view" now only opens in "SQL view";

Code:

SELECT Songs.SONG, Songs.ARTIST
FROM Songs
WHERE (IIF ([Forms]![SongSearch]![Tog_Song].[Caption]="starts with", (Songs.SONG) Like [Forms]![SongSearch]![TB_SongFilter] & "*", (Songs.SONG) Like "*" & [Forms]![SongSearch]![TB_SongFilter] & "*"))
ORDER BY Songs.SONG, Songs.ARTIST;

theDBguy · Oct 15, 2019

GreyArea said:
I fixed it a different way...this works perfectly...but the builder doesn't like it...tryingto open in "design view" now only opens in "SQL view";

Code:

SELECT Songs.SONG, Songs.ARTIST FROM Songs WHERE (IIF ([Forms]![SongSearch]![Tog_Song].[Caption]="starts with", (Songs.SONG) Like [Forms]![SongSearch]![TB_SongFilter] & "*", (Songs.SONG) Like "*" & [Forms]![SongSearch]![TB_SongFilter] & "*")) ORDER BY Songs.SONG, Songs.ARTIST;

Hi. Glad to hear you got it sorted out. Good luck with your project.

Pat Hartman · Oct 15, 2019

It would be better to build the SQL on the fly in your form when the user chooses his option. Then use the SQL String as the result form's RecordSource.

I'm surprised that what you came up with as a solution actually works. Test carefully.

Using IIF in queries (1 Viewer)

GreyArea

New member

theDBguy

I’m here to help

GreyArea

New member

theDBguy

I’m here to help

GreyArea

New member

theDBguy

I’m here to help

GreyArea

New member

theDBguy

I’m here to help

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread