Query Criteria Parameter 'Like Iff Between'

[Monsora83]

Im trying to get a parameter for my query to show all data in a query if a combo box is selectd with 'All', however if any other value is selected (numerical) I want it to show the data that is only between that selected value +- 30 each way (threshold tolerance).

Any idea on how I can fix this to make it work properly? The true value works properly, just not the false statement. It returns no data at all when false.

Like IIf([Forms]![Frm_Analysis]![Field1]="All","*",Between ([Forms]![Frm_Analysis]![Field1] -30) And ([Forms]![Frm_Analysis]![Field1]+30))

 

[JamesMcS]

Have you tried taking the like out?

 

[Monsora83]

Have you tried taking the like out?

If I remove the Like then it returns an error that its to complex during a true statement.

 

[JamesMcS]

I'm not sure if like and between work together - try

IIf([Forms]![Frm_Analysis]![Field1]="All","Like '*'","Between ([Forms]![Frm_Analysis]![Field1] -30) And ([Forms]![Frm_Analysis]![Field1]+30)")

as the criteria for your query. This is a bit of guesswork - I'm not sure if the iff statement just returns a string that ends up being used as the criteria or not, hence the quotes - they might have to come out...

 

[Monsora83]

I'm not sure if like and between work together - try

IIf([Forms]![Frm_Analysis]![Field1]="All","Like '*'","Between ([Forms]![Frm_Analysis]![Field1] -30) And ([Forms]![Frm_Analysis]![Field1]+30)")

as the criteria for your query. This is a bit of guesswork - I'm not sure if the iff statement just returns a string that ends up being used as the criteria or not, hence the quotes - they might have to come out...

Doesn't seem to enjoy having the Like "*" in the true/false section with or without the quotes.

Between doesn't want to work with like in the statement (tried that by itself as Like Between X And Y)

And it doesn't want to run Between in a true/false statement either it seems ( tried iif(1=1, Between X and Y, "NA") and got no results)

Thanks for trying though, worth a shot.

 

[JamesMcS]

Bugger.... only other thing I can think of is to run this in VBA and substitue a variable for the criteria, which in turn is populated by a select case statement:

Dim QryCrt as string

Select Case comboname.value

Case All

QryCrt = "*"

Case Else

QryCrt = "Between...."

End Select

docmd.runsql "SELECT.... WHERE fieldname=" & Qrycrt

 

[Brianwarnock]

Does text and numeric sit ok in the combo box?

I would use Null for all
Criteria

Where (fieldname Between ([Forms]![Frm_Analysis]![Field1] -30) And ([Forms]![Frm_Analysis]![Field1]+30) or [Forms]![Frm_Analysis]![Field1] Is Null)


Brian