Adding AND/ OR to strSQL

[motscotland]

Hi,
I have a search form that takes values from 7 combo boxes and builds an SQL query which then outputs a filtered list.

The code is :

Private Sub cmdOK_Click()
' Pointer to error handler
    On Error GoTo cmdOK_Click_err
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strqual1 As String
    Dim strqual2 As String
    Dim strqual3 As String
    Dim strDisc1 As String
    Dim strDisc2 As String
    Dim strDisc3 As String
    Dim strWork As String
    Dim strSQL As String
' Identify the database and assign it to the variable
    Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
    If Not QueryExists("qryStaffListQuery") Then
        Set qdf = db.CreateQueryDef("qryStaffListQuery")
    Else
        Set qdf = db.QueryDefs("qryStaffListQuery")
    End If
' Get the values from the combo boxes
    If IsNull(Me.cboQual1.Value) Then
        strqual1 = " Like '*' "
    Else
        strqual1 = "='" & Me.cboQual1.Value & "' "
    End If
        If IsNull(Me.cboQual2.Value) Then
        strqual2 = " Like '*' "
    Else
        strqual2 = "='" & Me.cboQual2.Value & "' "
    End If
        If IsNull(Me.cboqual3.Value) Then
        strqual3 = " Like '*' "
    Else
        strqual3 = "='" & Me.cboqual3.Value & "' "
    End If
        If IsNull(Me.cboDisc1.Value) Then
        strDisc1 = " Like '*' "
    Else
        strDisc1 = "='" & Me.cboDisc1.Value & "' "
    End If
    If IsNull(Me.cboDisc2.Value) Then
        strDisc2 = " Like '*' "
    Else
        strDisc2 = "='" & Me.cboDisc2.Value & "' "
    End If
        If IsNull(Me.cboDisc3.Value) Then
        strDisc3 = " Like '*' "
    Else
        strDisc3 = "='" & Me.cboDisc3.Value & "' "
    End If
    If IsNull(Me.cboWork.Value) Then
        strWork = " Like '*' "
    Else
        strWork = "='" & Me.cboWork.Value & "' "
    End If
' Build the SQL string
    strSQL = "SELECT tblCVq2.* " & _
             "FROM tblCVq2 " & _
             "WHERE tblCVq2.Qualifications" & strqual1 & _
             "AND tblCVq2.Qualifications" & strqual2 & _
             "AND tblCVq2.Qualifications" & strqual3 & _
             "AND tblCVq2.Discipline" & strDisc1 & _
             "OR tblCVq2.Discipline" & strDisc2 & _
             "OR tblCVq2.Discipline" & strDisc3 & _
             "AND tblCVq2.work" & strWork & _
             "ORDER BY tblCVq2.Work;"
' Pass the SQL string to the query
    qdf.sql = strSQL
' Turn off screen updating
    DoCmd.Echo False
' Check the state of the query and close it if it is open
    If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryStaffListQuery") = acObjStateOpen Then
        DoCmd.Close acQuery, "qryStaffListQuery"
    End If
' Open the query
    DoCmd.OpenQuery "qryStaffListQuery"
cmdOK_Click_exit:
' Turn on screen updating
    DoCmd.Echo True
' Clear the object variables
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
cmdOK_Click_err:
' Handle errors
    MsgBox "An unexpected error has occurred." & _
        vbCrLf & "Please note of the following details:" & _
        vbCrLf & "Error Number: " & Err.Number & _
        vbCrLf & "Description: " & Err.Description _
        , vbCritical, "Error"
    Resume cmdOK_Click_exit
End Sub

What I want to do is insert AND / OR option buttons between combo boxes on the form so the user can select different combinations for their query-

IE - Selection -

Qual1 AND/OR Qual2 AND/OR Qual3
AND/OR
Disc1 AND/OR Disc2 AND/OR Disc3
AND/OR
Work

I hope the problem is clearly explained - I am a relative newbie to Access and SQL/VBA so fairly explicit advice required please!
Thanks in advance for your help.

Cheers the now,
Myles

 

[FoFa]

How will you handle the grouping?
Say they want (CB1 AND CB2 AND CB6) OR (CB2 AND CB6 AND CB7) which is not the same as CB1 AND CB2 AND CB6 OR CB2 AND CB6 AND CB7
Humm?
Just a thought before you offer up that option.

 

[motscotland]

Thx FoFa,

The idea of the search form is to find appropriate people for upcoming contracts from our current database of candidates.

So, I am attempting to make the form as versatile as possible. Ideally I need to give the option to bracket certain criteria as you mentioned FoFa -

ie-
(Qual - 1 AND/OR 2 AND/OR 3)
AND/OR
(Disc - 1 AND/OR 2 AND/OR 3)
AND/OR
Work

BUT there might also be need for the grouping -

((Qual 1 AND/OR 2) AND/OR Qual 3)
AND/OR
((Disc - 1) AND/OR Disc 2 AND/OR 3)
AND/OR
Work

How can I give the user these options? A previous version of the db was done in Fox Pro and had 'bracket boxes' to tick and an AND/OR toggle button which did the job. From a users POV it would be handy to retain these features in the new Access version.

Thanks in advance!

Cheers,
Myles

 

[motscotland]

After searching the forums and some serious head scratching I now have this:

'Check for the controls to give AND/OR type
    Dim strJoinType As String
    Const conMAXCONTROLS = 9
    For i = 1 To conMAXCONTROLS - 1
        If Me("optClause" & i) = 1 Then
        strJoinType = "AND"
        Else
        strJoinType = "OR"
    End If
    Next

I have tested this and it works grand - I got the right answers in a Msgbox placed in the loop.

However, I want to take these values and somehow pass them to the strSQL (in place of the AND s)

' Build the SQL string
    strSQL = "SELECT tblCVq2.* " & _
             "FROM tblCVq2 " & _
             "WHERE tblCVq2.Qualifications" & strqual1 & _
             "AND tblCVq2.Qualifications" & strqual2 & _
             "AND tblCVq2.Qualifications" & strqual3 & _
             "AND tblCVq2.Discipline" & strDisc1 & _
             "AND tblCVq2.Discipline" & strDisc2 & _
             "AND tblCVq2.Discipline" & strDisc3 & _
             "AND tblCVq2.work" & strWork & _
             "AND tblCVq2.Town" & strTown & _
             "AND tblCVq2.Status" & strStatus & _
             "ORDER BY tblCVq2.Work;"
' Pass the SQL string to the query
    qdf.sql = strSQL

Please help!! I have got this far with some invaluable help from these forums. Thanks millions peeps!

Cheers all,
Myles

 

[FoFa]

I would change your AND/OR control detect code to a function, pass the control you want into the function, have the function pass AND or OR back. You will have to play with it to get it to work, maybe even have to pass the form itself into it, maybe something like:

StrSql = "SELECT blah, blah' & _
MyFunct(CheckBox1,Form1) & " tblCVq2.Qualifications" & strqual2 & _
MyFunct(CheckBox2,Form1) & " tblCVq2.Qualifications" & strqual3 & _
Etc.

But that is no help with groupings, that is a little tougher.

 

[motscotland]

Thanks.
I have spent a long while this morning 'playing' with the function......AAARGH!!! It doesn't work! I don't know how to relate the two together.

Function

Public Function JoinType(strJoinType As Long)
 'Check for the controls to give AND/OR type
        If JoinType() = 1 Then
        strJoinType = "AND"
        Else
        strJoinType = "OR"
    End If
End Function

Main procedure where function is to be called from....

' Build the SQL string
    strSQL = "SELECT tblCVq2.* " & _
             "FROM tblCVq2 " & _
             "WHERE tblCVq2.Qualifications" & strqual1 & _
             "AND tblCVq2.Qualifications" & strqual2 & _
             "AND tblCVq2.Qualifications" & strqual3 & _
             "AND tblCVq2.Discipline" & strDisc1 & _
             "AND tblCVq2.Discipline" & strDisc2 & _
             "AND tblCVq2.Discipline" & strDisc3 & _
             "AND tblCVq2.work" & strWork & _
             "AND tblCVq2.Town" & strTown & _
             "AND tblCVq2.Status" & strStatus & _
             "ORDER BY tblCVq2.Work;"
' Pass the SQL string to the query
    qdf.sql = strSQL

I tried this....

   strSQL = "SELECT tblCVq2.* " & _
             "FROM tblCVq2 " & _
             "WHERE tblCVq2.Qualifications" & strqual1 & _
             JoinType(optClause1, frmCVQuery) & " tblCVq2.Qualifications" & strqual2 & _
'.....etc.......

but it didn't produce anything.

Please guide me as to how I feed the optClause bit into the function??? I have searched the forums but did not locate what I need to know.

Thanks in advance.
Cheers,
Myles

 

[FoFa]

Public Function JoinType(strJoinType As Long) AS STRING
'Check for the controls to give AND/OR type
If strJoinType = 1 Then
JoinType = "AND"
Else
JoinType = "OR"
End If
End Function

ALSO this is incorrect
JoinType(optClause1, frmCVQuery)
as you only are specifing one parameter in your function (strJoinType)

 

[motscotland]

Thx m8.
It works a treat!!

A nice way to end the week.

Cheers,
Myles