Iif not working as a part of criteria

[crobaseball]

I'm using this expression in the criteria of a query

IIf([FBA? Y/N]="Y",>0,=0 Or Is Null)

I'm using a parameter [FBA? Y/N] which I've defined as text. I've used >0 and =0 Or Is Null as criteria without the Iif and it works fine. What the heck am I missing?

[June7]

Is this in query criteria? Cannot have dynamic operators (>, <, =, etc).

Recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

[crobaseball]

Yes, it's a query criteria.

If I need to use a dynamic operator based on the input of the parameter, how can I do this?

[June7]

Don't use dynamic parameterized query - I NEVER do.

Review: http://www.allenbrowne.com/ser-62.html

[crobaseball]

Ok, so use a form to create this instead? Geez, I don't want to make it that complicated. I'm the only one who will use the thing but don't want to fill in the criteria in the query each time I run it

[June7]

Not aware of any other options.

[crobaseball]

I get it. Access forces rigorous integrity. Part of using it. But a pain in the a$$ in this case

[crobaseball]

I guess I could just generate the table, then sort by values, but I like to have the sorting done so the query just comes through clean.

[CJ_London]

you can use vba to build your query sql to pass to a querydef or if your table is the recordsource to your form, build a filter

e.g.

form recordsource=tblData

have a button to create your filter based on whatever parameters you are using

I'll assume you want FBA to be text (Y or N) and is an unbound control called FBA, the code to build the button would be

me.filter="[SomeField] " & IIf([FBA]="Y",">0","=0 Or [SomeField] Is Null")
me.filteron=true