If statements to set parameters in a QUERY

[leedub]

If and ELSE statements in a QUERY

Hello im working in MS Access 2007.

Ok so what im trying to do is have a query run specific parameters if a check box is selected. So if the check box is selected than the query filters the "Tranche" column so that the only records that shown are records that have the "Tranche" coloumn = 1.

I know this can be done either in vba code or in the criteria section of a query but i dont know that appropriete language for either.

In vba code i was able to get this far. But..... i dont know how to call the criteria line from a query????

This is my very simple unfinished code.

My query is called [Tranch Query] and the column i want to filter with is call [Tranche]. The check box is called [Check0].

Private Sub Check0_AfterUpdate()

If Check0 = True Then
'How do i set the query criteria?????
End Sub

Any help is apreciated. Thanks in advance!

 

[leedub]

Im open to doing this in the query as well if that's easier than the VBA.

 

[CraigDolphin]

This would be one simplistic VBA approach...this assumes you have the DAO reference set in the VBA editor.

Private Sub Check0_AfterUpdate()
   Dim strSQL as String
   Dim qdf As DAO.QueryDef

   If Me.CheckBoxName Then 
         [COLOR="PaleGreen"]'use a criteria[/COLOR]
         strSQL="Select * from [COLOR="Blue"]YourSourceTableName[/COLOR] Where [Tranche]=1;"
   Else 
         [COLOR="PaleGreen"]'don't use a criteria[/COLOR]
         strSQL="Select * from [COLOR="Blue"]YourSourceTableName[/COLOR];"
   End If

   Set qdf = CurrentDb().QueryDefs("[COLOR="Red"]YourQueryName[/COLOR]")
   qdf.SQL = strSQL
End Sub

Some things to consider:
Don't forget to add error handling

Make sure you replace the blue/red names with the names of the relevant objects in your database.

If there are more than two scenarios to consider, use the 'Select Case' test instead of an 'if-else' test.

You could replace the value that is used as the criterion in the Tranche column with a user-entry parameter...

strSQL="Select * from YourSourceTableName Where [Tranche]=[Input Value];"

or a reference to a textbox on a form or whatever...

strSQL="Select * from YourSourceTableName Where [Tranche]=" & Me.SomeTextBox & ";"

Consider adding code to check for the existence of the query object or table if there's any chance of it not being present.

 

[leedub]

Thanks so much that works great! Unfortunately my goal has changed slightly.

I need multiple check boxes to filter the query. So ill have

Check0 - if selected filter query so that "Tranche" = 1
Check2 - if selected filter query so that "Tranche" = 2
Check3 - if selected filter query so that "Tranche" = 3

Check4 - if selected filter query so that "Must Do" = yes

Check5 - if selected filter query so that "Project Area" = 'West Ells'
Check6 - if selected filter query so that "Project Area" = 'Muskwa'

And the user can select any combination of check boxes to filter the query. So if for example they check of "Check0" and "Check2" the query shows all records where "Tranche" = 1 OR 2.

Any advice or help would be apreciated. Im trying to fiddle with the code you have supplied me with but it seem to specific.

Thanks in advance!!!

 

[Brianwarnock]

On the successive rows of the criteria code

Iif(forms!formname!check0=True,1)
Iif(forms!formname!check2=True,2

Etc

Brian

 

[Brianwarnock]

Pat I was answering here based on his other thread, the tranches are not mutually exclusive and this is the area he seemed to be having trouble with, he can want to select any combination of values.

Brian

 

[Brianwarnock]

As I said I was actually responding to his other thread

http://www.access-programmers.co.uk/forums/showthread.php?t=250905

but the requirement is the same, however if I had read this thread properly I would have replied with a SQL view.
I do not see how this requirement can be met from a single combo

And the user can select any combination of check boxes to filter the query. So if for example they check of "Check0" and "Check2" the query shows all records where "Tranche" = 1 OR 2.

My approach gives SQL of

Where (Tranche)=IIf(forms!form1!check0=True,1) Or (Tranche)=IIf(forms!form1!check2=True,2) Or (Tranche)=IIf(forms!form1!check3=True,3)
And etc

which allows for any combination of the 3 checkboxes.

He should definitely use a combo for the project area.

Brian