Conditional Query with conditional results

[Jeff_in_KCMO]

I am querying table1, field1 which is a number value.

I have a form (form1) where I am entering the max (field1Max) and min (field1Min) values for field1 to be queried.



I need help creating a query that basically does this, which does not work: iif(forms.form1.field1Min is null AND forms.form1.field1Max is not null, table1.field1 <= forms.form1.field1Max,0)

so basically, if the user does not input a value for field1Min, but does input a value for field1Max, the query will return all records where table1.field1 <= field1Max.

[ranman256]

You would need a condition on which Qry to open:

dim vQry

select case true
   case isnull(field1Max) and isnull(field1Min)
        vQry = "qsAll"
   case isnull(field1Max) and not isnull(field1Min)
        vQry = "qsOnlyMin"
   case not isnull(field1Max) and isnull(field1Min)
        vQry = "qsOnlyMax"
   case else
        vQry = "qsBothMinMax
end select

docmd.openquery vQry

[Jeff_in_KCMO]

I haven't mentioned that the above is just a small part of a larger query. Keeping that in mind, how would I go about putting your suggestion into the primary query?

And, my apologies for my ignorance, and appreciation for the help.

[June7]

Are the fields text type? Can try LIKE and wildcard: http://www.datapigtechnologies.com/f...earchform.html

I don't use dynamic parameterized queries. I prefer to apply filter to form or report: http://www.allenbrowne.com/ser-62code.html

[Jeff_in_KCMO]

June7, fields are a mixture of text, number, and dates.

Unfortunately IT keeps me from opening the datapigtechnologies link.

I will have to take a close look at the allenbrowne page. I'm working on this on my own time between actual work.