Combo Box SQL Query

[kevinnaomi]

I have created a form that has combo boxes for several columns in a table. I have also created a query that selects the records that have the selected four combo boxes values:

SELECT *
FROM costs
WHERE
(((costs.Category)=[Forms]![Search Form]![Category]) AND ((costs.Action)=[Forms]![Search Form]![Action]) AND
((costs.Type)=[Forms]![Search Form]![Type]) AND
((costs.[Sub Type])=[Forms]![Search Form]![Sub Type]));

I was wondering if anyone knows how to change this so that I can select any number of the boxes and the query will still work. I would like to be able to get results from one or two or three columns, as well as all four. If anyone could help, it would be greatly appreciated.

 

[ML1999]

Assuming that this is a standard query (ie written in the query builder and not part of some VBA code), you will need to use the immediate IF (IIF()) statement to test for the presence of a selection in the combo boxes.

To do this you will need to enter the function in the criteria row of the query builder. Hence you will have something along the lines of ...

The costs.Category column -
'LIKE IIF([Forms]![Search Form]![Category]<>&quot;&quot;,[Forms]![Search Form]![Category],&quot;*&quot'

The costs.Action column -
'LIKE IIF([Forms]![Search Form]![Action]<>&quot;&quot;,[Forms]![Search Form]![Action],&quot;*&quot'

Repeat this for as many columns in you query that you want to add parameters to.

 

[kevinnaomi]

ML1999,
thank you for this. But I am not sure how it should look.
I edited it to look like:
SELECT *
FROM costs
LIKE IIF([Forms]![Search Form]![Category]<>&quot;&quot;,[Forms]![Search Form]![Category],&quot;*&quot
LIKE IIF([Forms]![Search Form]![Action]<>&quot;&quot;,[Forms]![Search Form]![Action],&quot;*&quot
LIKE IIF([Forms]![Search Form]![Type]<>&quot;&quot;,[Forms]![Search Form]![Type],&quot;*&quot
LIKE IIF([Forms]![Search Form]![Sub Type]<>&quot;&quot;,[Forms]![Search Form]![Sub Type],&quot;*&quot
But I received some errors. Could you tell me what is wrong with this. I know it is me...but could you help me correct the syntax. hugely appreciated.

 

[ML1999]

Try cutting and pasting this SQL statement:

SELECT costs.Category, costs.Action, costs.Type, costs.[sub type], *
FROM costs
WHERE (((costs.Category) Like IIf([Forms]![Search Forms]![Category]<>&quot;&quot;,[Forms]![Search Forms]![Category],&quot;*&quot) AND ((costs.Action) Like IIf([Forms]![Search Forms]![action]<>&quot;&quot;,[Forms]![Search Forms]![action],&quot;*&quot) AND ((costs.Type) Like IIf([Forms]![Search Forms]![type]<>&quot;&quot;,[Forms]![Search Forms]![type],&quot;*&quot) AND ((costs.[sub type]) Like IIf([Forms]![Search Forms]![sub type]<>&quot;&quot;,[Forms]![Search Forms]![sub type],&quot;*&quot));

 

[telephoto]

HI

sounds similar to thread181-475018, if you need my code then I can supply

Telephoto

 

[kevinnaomi]

Telephoto,
I had a look at the thread you recommended.
It sounds like the code you have does exactly what I am looking for. Could I ask you to post the code for me? It would save my life, well, so to speak.
I also wanted to ask.. this code.. should it be in the query or in the VB of the form?

 

[kevinnaomi]

Dear All,
Thank you for your help on this.
I edited the values a little and everything works good....
I really appreciate the help. The final result looks like this:
SELECT *
FROM costs
WHERE ((([costs].[Category]) Like IIf([Forms]![Search Form]![Category]<>&quot;&quot;,[Forms]![Search Form]![Category],&quot;*&quot) And (([costs].[Action]) Like IIf([Forms]![Search Form]![action]<>&quot;&quot;,[Forms]![Search Form]![action],&quot;*&quot) And (([costs].[Type]) Like IIf([Forms]![Search Form]![type]<>&quot;&quot;,[Forms]![Search Form]![type],&quot;*&quot) And (([costs].[Sub Type]) Like IIf([Forms]![Search Form]![Sub Type]<>&quot;&quot;,[Forms]![Search Form]![Sub Type],&quot;*&quot) And (([costs].[Vendor]) Like IIf([Forms]![Search Form]![vendor]<>&quot;&quot;,[Forms]![Search Form]![vendor],&quot;*&quot));

I have found one more problem though....when I click the button which runs a macro which says run the query the results come as expected. However, when the form is closed, a new record is created in the table with the entries from the search....anyone have any idea why this would happen and perhaps how to fix it.......
I tried taking the macro out of the circle and linking the button directly to the SQL search, but this had no effect.

 

[telephoto]

kevinjsdg

The way this works is to slowly build up the sql statement, the beautyof that is that any extra criteria can be added with minimal effort

OK here goes, should have enough notes....

'I have a form with several check boxes, text boxes and y/n boxes.  Any or all may have a criteria set.
' A tblAnswers holds the original data, which is interrogated and the result put into a temporary table to be sent to Excel for number crunching. (The Excel bit has been left off to simplify it).

'The criteria for the search are set and then an on click event starts the code:



Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

DoCmd.SetWarnings False

Call SortData ' a routine which runs the search

'OK now you have your new temp table, do what you like with it.


Exit Sub

Err_cmdSearch_Click:
    MsgBox Err.Description
    Resume Exit_cmdSearch_Click
    
End Sub


Private Sub SortData()

Dim Rnk As String ' combo
Dim Brnch As String 'combo
Dim TimeIn As Integer 'cbo
Dim WShop As Date ' text box
Dim MenOnly As Boolean 'check box
Dim WomenOnly As Boolean 'checkbox
Dim sqlstr As String 'gradually increasing sql string to be used as the final search query
Dim Temporary As Variant 'use as the temp holder of the value in the routine
Dim n As Integer ' counter to see if any boxes were checked at all, if none checked final value = 0

'This was used to make a temp table to export data to Excel
'so to clear the temp table ready for new data
sqlstr = "DELETE tblTemp.* FROM tblTemp;"
DoCmd.RunSQL sqlstr


n = 0

'start of sql to make temp table, tis will be built up as various selection options are checked
'finally the end of the expression is added

sqlstr = "INSERT INTO tblTemp SELECT tblAnswers.* FROM tblAnswers WHERE(("

'If a value is selected in these cbo's then the criteria will be added to the sql and n<>0

If cboRankGrade.Value <> &quot; &quot; Then
Temporary = Rnk
sqlstr = sqlstr + &quot;(tblAnswers.rank)='&quot; & Temporary & &quot;') &quot;
n = n + 1
End If
If cboBranch.Value <> &quot; &quot; Then
Temporary = cboBranch.Value
sqlstr = sqlstr + &quot;(tblAnswers.post)='&quot; & Temporary & &quot;') &quot;
n = n + 1
End If

'setting for time in was with option radio buttons, stored as integers. I am 
'reusing n because I don't care what value it finishes if it's greater than 1

If cboTimeIn.Value <> &quot; &quot; Then
Temporary = cboTimeIn.Value
    If Temporary = &quot;Less than 1 year&quot; Then
       n = 1
        ElseIf Temporary = &quot;Between 1 and 3 years&quot; Then
        n = 2
        Else: Temporary = 3
    End If
    sqlstr = sqlstr + &quot;(tblAnswers.TimeIn)=&quot; & n & &quot;) &quot;

End If
If txtWorkshopDate <> &quot; &quot; Then
Temporary = txtWorkshopDate.Value

'set date value into format suitable for sql

Temporary = Format(Temporary, &quot;mm/dd/yyyy&quot;)
sqlstr = sqlstr + &quot;(tblAnswers.workshopdate)=#&quot; & Temporary & &quot;#) &quot;
n = n + 1
End If
'final addition to round off the sql statement
sqlstr = sqlstr + &quot;);&quot;

If n = 0 Then 'if n=0 then no selection criteria, so select whole table
sqlstr = &quot;INSERT INTO tblTemp SELECT tblAnswers.* FROM tblAnswers;&quot;
Else
End If
Debug.Print sqlstr
DoCmd.RunSQL sqlstr
n = 0
'Use these after main selection is completed if men or women only selected
    If chkMen = True Then
        If chkWomen = True Then
        MsgBox &quot;Do you really mean that???&quot; 'I could have used option boxes. But this was more fun.
        For n = 1 To 10000
        Next
        MsgBox &quot;No records selected&quot;
        Exit Sub
        End If
    End If

'Again, this could have been in the code above, but it just runs queries to delete the data not required in the temp table

If chkMen = True Then
DoCmd.OpenQuery (&quot;qryDelWomenFromTemp&quot;)
ElseIf chkWomen = True Then
DoCmd.OpenQuery (&quot;qryDelMenFromTemp&quot;)
Else
End If

End Sub

Telephoto