Passing operators into parameter queries?

[Whizzo]

Hi all;
Well, I've had my obligatory hour of Googling, scratching my head and trial and error and now I'm stumped. I'm using the following code as a query parameter:

ALike IIf(IsNull([forms]![frmReportGenerator]![cboMinTotExp]),"%",<[forms]![frmReportGenerator]![cboMinTotExp])

The idea is that if there's value in the field then the parameter is carried into the query. If the box is empty then it's not. Using:

<[forms]![frmReportGenerator]![cboMinTotExp]

as a parameter on its own works fine but then I lose the vital iif() function. But using the iif() function returns no results! When I check back on the query I find that Access has rewritten it to:

ALike IIf(IsNull([forms]![frmReportGenerator]![cboMinTotExp]),"%",([tblCustomers].[TotalExposure])<[forms]![frmReportGenerator]![cboMinTotExp])

which is obvioulsy fishy. Any ideas how I can phrase the original statement in a way that'll work? It's driving me nuts!

[FishVal]

Ignore blank search parameter with IIf statement

[Whizzo]

Close but no cigar. The "blank search parameters" problem was one I solved last week, that at least is working fine now. My problem is how to fit < and > into the TruePart of an iif statement, where the TruePart is a control on a form. Any ideas?

[FishVal]

The last post in the thread has a link to other thread. I guess your answer is there.

[NeoPa]

You're using IIf() to determine which value to use, yet that's not what your code is doing. It's also trying to change the comparator operator, which doesn't make sense as it's already included as "ALike" (Using the Right Wildcard Characters in SQL Statements may help).

Can you post the two different WHERE items (including A Op B) that you would expect in the two circumstances so that I can think what it is you may be looking for.

Is this SQL stored as is, or are you working it out in code to be executed afterwards?

[NeoPa]

@Whizzo
I think your problem is more to do with thinking that even makes sense.

As Mr Spock might say "It's illogical Jim".

[Whizzo]

Sure it makes sense! Here's how I understand it. An iif statement is made of:
1. Criteria. String to check for validity.
2. Truepart. Return this string/value if (1) is True.
3. Falsepart. Return this string/value if (1) is False.
In this case:
1. Criteria is "Is this [txtTest] empty?"
2. Truepart. "If [txtTest] is empty, return a "%" here." (in this case it's a Criteria box in the Query Builder)
3. Falsepart. "If it's not empty, return "<[txtTest]" (or <"[txtTest]", whichever it may be) into this space.

That makes sense, doesn't it? My problem is getting the iif() statement to phrase the Falsepart in a way that the Query Builder can understand and work with. I'm currently using the above iif method with plain text that doesn't need < and > and its working perfectly. Any ideas? I'm a doctor Jim, not a programmer!

[FishVal]

Use operators in textbox for query.

[Whizzo]

Thanks fish, that's almost the tree I'm barking up but I don't need to let users enter their own operators. The user will supply the number (say 500) and then the iif() statement will supply a Less Than operator. I did try

< IIf(IsNull([forms]![frmReportGenerator]![cboMinTotExp]),"%",<[forms]![frmReportGenerator]![cboMinTotExp]) 

instead of starting with Alike but the expression was too complicated for Access to process, apparently.

[NeoPa]

@Whizzo
No (My previous post was a clue).

Why it doesn't make sense may not be so easy to see without direction.

Consider your last statement :
@Whizzo
As this is SQL we're working within (NOT some VBA code to create a string which later may be passed to and interpreted by the SQL engine), the comparator (<) within the string, is simply another character of the data. The fact that it is within the return value of a function call (IIf()), tells the SQL engine that it is to be treated as data and not as part of the command structure.

Consider that the return value can be considered to be equivalent to a literal string. In that case it would be equivalent to :

... ALike '<[txtTest]'

NB :

1. The '<' is within the text quotes.
2. Even if it weren't, you would still end up with 'ALike' and '<' which would not be something the SQL engine could logically process.