Query with Dynamic Criteria

[theosgood]

I have a form with source = query

Query is against a single table with the fields CentEnding(text) and Exclude7(yes/no). Exclude7 will either be Yes or No for all records regardless of value in CentEnding. And unfortunately, CentEnding cannot be changed to numeric if it makes it easier.

I want to limit the records returned by the source query by setting the criteria for CendEnding to be, if Exclude7 = Yes then <> “97” (exclude 97), if Exclude7 = No then > “0” (all records).

I’ve tried the following but keep getting a “data type mismatch in criteria” error:
IIf(Exclude7 = -1, <>”97”, >”0”)
IIf(Exclude7 = -1, “<>”97””, “>”0””)
IIf(Exclude7 = -1, <>&”97”, >&”0”)
IIf(Exclude7 = -1, “<>”&”97”, “>”&”0”)
IIf(Exclude7 = -1, “<>&”97””, “>&”0””)
IIf(Exclude7 = -1, &<>&”97”, &>&”0”)

I could write a VBA module that builds the query dynamically but I can’t find anything on how to use a module as the source for a form.

Suggestions please.

[JoeM]

I could write a VBA module that builds the query dynamically but I can’t find anything on how to use a module as the source for a form.

If you can create the SQL code for the query in VBA, you can assign that SQL code to a pre-defined query (the one your Form is based on).

So let's say in your VBA you build the SQL code and assign it to a variable named "mySQL". And your Form is based on a query called "myQuery".
Then, in VBA, you can update the SQL code of myQuery with the SQL code you just built like this:

           CurrentDb.QueryDefs("myQuery").SQL = mySQL

You could then, in turn, open your Form in the same VBA code immediately after it, like this:

    DoCmd.OpenForm "myForm"

[theosgood]

Thanks. Your suggestion worked well for defining the query. However, it is in a subform that is part of a tabbed form and I am having issues getting it to refresh when the tab is selected. I appreciate your help and will post a new thread in forms.