IIf Then Else Between

[romanzero]

Hi,

I have a query that returns results based upon a user's selection of different fields off of a form.

The query works fine except for when I am trying to reference two fields to obtain results greater that [FIELD_1] but less than [FIELD_2].

It works when the Where section of the SQL only contains those two fields:

Between [Forms]![Form_A]![FIELD_1] And [Forms]![Form_A]![FIELD_2]

However...

When I add an IIf statement such as:

Like IIf([Forms]![Form_A]![FIELD_3]<>"xyz","*",Between [Forms]![Form_A]![FIELD_1] And [Forms]![Form_A]![FIELD_2])

I can get the results of the "*" when [FIELD_3] does not equal "xyz", but when it does equal "xyz" and I enter criteria I get no returns.

I must be missing something in the SQL???

Any help would be greatly appreciated.

 

[Golom]

If [Forms]![Form_A]![FIELD_3]<>"xyz"is TRUE then the statement resolves to

Like "*"

and if it is FALSE

Like Between [Forms]![Form_A]![FIELD_1] And [Forms]![Form_A]![FIELD_2]

which I'm suprised even runs. Perhaps you want

IIf( [Forms]![Form_A]![FIELD_3]<>"xyz",
     Like '*',
     Between [Forms]![Form_A]![FIELD_1] And [Forms]![Form_A]![FIELD_2])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

Or perhaps this ?
Between [Forms]![Form_A]![FIELD_1] And [Forms]![Form_A]![FIELD_2] OR [Forms]![Form_A]![FIELD_3]<>'xyz'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[romanzero]

Thanks for the responses.

I should have clarified that [FIELD_3] has four choices. 1, 2, and 3 should return all results regardless of any value typed in [FIELD_1] and [FIELD_2].

In fact I have VB code that hides those fields unless the user selects the forth option - "xyz". At that point [FIELD_1] and [FIELD_2] become visible and the user is promted to enter something.

"Please select the range of data to be queried."

I have tried to add "Like" before the "Between" statement. That doesn't work.

> [FIELD_1] and < [FIELD_2]

 

[romanzero]

Thread 701-1065394 got me what I wanted.

Thanks for your help.

 

[PHV]

Thread701-1065394 got me what I wanted
In this thread I gave similar reply than here !