Revise Query structure

[ProteanBeing]

I have a query that filters a table and is used to generate a report. I would like to be able to change the parameters of the query programmically. For example, there is a Yes/No field called Sintered Scrap. I would like to choose (in a form) to sort for True, False, or All. In the event of All I would remove the WHERE condition in the SQL statement. I had thought about running this through a VBA sub-routine. The only thing I am lacking is that once I have the SQL statement build in a string variable, how do I save it as a query.

[OBP]

It doesn't have to done with VBA and SQL, it can be done just using the Query's Criteria rows
Assuming that you place an Option Group on your Form called Frame0 and it has
Sintered Scrap = 1
No Sintered Scrap = 2
All Records = 3
in the query add another column with the heading as
option: forms![name of your form]![Frame0]
in that columns criteria row enter
1
2
3
in the Sintered Scrap column enter
-1
0
and nothing for the 3rd row - ie all records

[ProteanBeing]

This works fine if you have one frame. However my application has several. Do you know how to work around this issue?

[OBP]

How many combinations can you have?
I have queries with 60+ working OK.

[OBP]

Anyway, I think this does what you want, you will just have to build the SQL statements based on your Frame selections

Private Sub Command2_Click()
Dim rs As Object, sql As String, qdfNew As Object
With CurrentDb
.QueryDefs.Delete "NewQueryDef"

Set qdfNew = .CreateQueryDef("NewQueryDef", _
"SELECT * FROM Categories WHERE [CategoryID]> 10 ")
DoCmd.OpenReport "Categories Query", acViewPreview
End With
End Sub


you have to create the Query called "NewQueryDef" or rem out the line of code that deletes it the first time you run this.

[ProteanBeing]

THANKS A LOT!!!!!!! THIS IS EXACTLY WHAT I NEEDED!!! YOU'RE THE BEST

[OBP]

Can you mark the thread as "Solved" please?