If statements to set parameters in a QUERY (1 Viewer)

[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

 

[Pat Hartman]

Since the tranches are mutually exclusive, use a single field. Either a combo or an option group that returns the values 1, 2, etc. Then you don't need an IIf() at all.

Select ...
From ...
Where (Tranche = Forms!myform!TrancheNum OR Forms!myform!TrancheNum Is Null)
AND ProjectArea = '" & Forms!myform!ProjectArea & "'" OR Forms!myform!ProjectArea Is Null)
AND fld3 = Forms!myform!fld3 OR Forms!myform!fld3 Is Null)
AND .....;
If TrancheNum is null, all tranches will be selected. If ProjectArea is selected, it will be used if not it will be ignored, fld3 is some other additional criteria, I just included to show how to expand the criteria.

 

[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

 

[Pat Hartman]

If they're not mutually exclusive then he can add additional AND (...) statements. I don't understand where you're going with the IIf()s yuu posted. Perhaps you could be more explicit.

 

[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