Like Statements within IIF statement Help...

[erik6408]

I am trying to use the IIF statement to look for a null value. That seems to be working fine..

The problem occurs when I use a LIKE Statement inside the IIF statement.
Here are some examples:

Like "*" & [Forms]![Search Form]![Title] & "*"   [B] This works fine[/B]

but the above statement used inside an IIF statements returns nothing as in the statement below.

IIf(IsNull([Forms]![Search Form]![Title]),Null,Like "*" & [Forms]![Search Form]![Title] & "*")            
[B]The above statement does not work and I know the IsNull expression is working,[/B]

For some reason the LIKE statement doesn't seem to work when used inside the IIF statement...

ANY help would be appreciated. Thank you..

[missinglinq]

I'm sorry, but your logic here really doesn't make any sense at all! What you're saying here is:

If [Forms]![Search Form]![Title] is empty (Null) you want some value to be Null.

If [Forms]![Search Form]![Title] is not empty (Null) you want some value to be

Like "*" & [Forms]![Search Form]![Title] & "*"!

This makes no sense!

Like is typically used when you are searching for a string within a larger string. It is not used to assign a value to something.

Exactly what, in plain language, are you trying to do here?

Welcome to Bytes!

Linq ;0)>

[erik6408]

Sorry for the confusion. I am trying to have users enter in a search value in a form to search for an item in the database. I do not want it to be an exact value but use wildcards to make it so that if their search value is any part of the text field it will return that record..

I would like to use a form with 5 text boxes that the user can enter search values in one or up to all five text boxes and then conduct a search. If they leave the text field blank I want it to not pull any records.

I am sure I am missing something.. Thanks for any help..

[NeoPa]

What you're missing is the ability to express your question clearly. You're not alone in this. Don't worry.

It seems you are trying to set up a SQL string, but you chopped off too much of the code around that would have given the context within which we could more easily have understood what you were asking for.

Your problem is a simple and oft repeated one. You are not creating a string value to return as you need. Check out below for help with this :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

Let us know how you get on with this.