Using Like * - Probably very simple!

[MikeDuffield]

Hi guys & girls,

I'm building a query, that looks in a couple of Combo Boxes and a couple of Text Boxes for data, then displays info based on what it finds.

Mostly the query just looks for things like "mduffield" (ie, the username) and it works just fine.

However, I'm trying to make a keyword search that uses the "Like", as follows:

Like "*" & [Forms]![question]![questiontxt] & "*"

Now, my problem is, when I run the query it will show all my data because "questiontxt" is empty. If I then enter data into "questiontxt" and re-query, it works.

What I'd like the query to do is ignore that criteria if "questiontxt" is blank. Is there an easy way of doing it?

Apologies if this is really obvious, I'm quite new to queries - I've used VB mainly!


Thanks in advance.


Regards,
Mike.

 

[plog]

What you need to do is test to see if [Forms]![question]![questiontxt] is blank using an IIF() statement. This is going to require a new calculated field in your query and the removal of the code you posted in the criteria section. So, first remove this code from your query:

Like "*" & [Forms]![question]![questiontxt] & "*"

And add the below new field in your query:

ValidQuestionTxt: IIf(IsNull([Forms]![question]![questiontxt]), True, IIf([QueryFieldNameToCompareToquestiontxt] LIKE "*" & [Forms]![question]![questiontxt] & "*", True, False))

Then beneath that, set the criteria to True.

 

[MikeDuffield]

Thanks Plog, that should do the trick!