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;
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?
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?