Build SQL String based on user's criteria 1

[bernie10]

Hey guys,
I have a query for which I need to adjust the where clause programtically depending on a value the user selects from a form. I just need a little help with the syntax. Could somebody just give a general example of how this could be done.

So in psuedocode:

Strsql = "SELECT...." [green] 'I think I can do all this without problem [/green]
If Condition 1 then
Strsql = Strsql & "WHERE Criteria 1..."
Else
Strsql = Strsql & "Where Criteria 2..."
End if
Set MyQuery based on Strsql [green] 'This is what I'm not sure how to do [/green]

Sorry I know this is basic and that there are many examples of this throughout the forums, but I'm new to this and wasn't sure exactly which method was best to use.

Thanks for the help,
Collen

 

[PHV]

How is this query launched ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bernie10]

Thanks for the reply.

Eventually it will be launched from a subform.

Collen

 

[PHV]

With the OpenQuery method or as a RecordSource for a form/report ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bernie10]

Sorry, as the Recordsource for the form.

I think that answered my question. So I guess I can just set the recordsource of the form programtically, right?

If Condition1 then
  Forms!MyForm.Recordsource = "SELECT Myquery.Field1, Myquery.Field2.... FROM Myquery " _ &
  "WHERE Criteria1..."
Else
  Forms!MyForm.Recordsource = "SELECT Myquery.Field1, Myquery.Field2.... FROM Myquery " _ &
  "WHERE Criteria2..."

If this is right then my only other question would be how to do it with a subform. I tried the syntax:

Forms!MyForm!MySubform.Recordsource = "SELECT..."

but gave the error "Object doesn't support this method".

Thanks for the help,
Collen

 

[PHV]

Forms!MyForm!MySubform.Form.Recordsource = "SELECT..."

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bernie10]

Perfect, thanks PHV.

Collen