I am creating a query builder for a database which uses list boxes to select multiple fields within each category. The only issue I am having is building the SQL query, i have tried several different things, and here is the latest code
Code:
Private Sub cmdGo_Click()
Dim strCritString As String
Dim strBuildString As String
Dim strFullString As String
Dim intPosWhere As Integer
Dim intPosSemi As Integer
Dim qd As QueryDef
Dim rst As DAO.Recordset
Dim frm As Form
Dim booFirstFlag As Boolean
Dim intSelItem As Variant
booFirstFlag = False
Set frm = Forms![StudInfoQuery]
Set qd = CurrentDb.QueryDefs("qryStudInfo")
strFullString = qd.SQL
intPosWhere = InStr(1, strFullString, "WHERE")
intPosSemi = InStrRev(strFullString, ";")
If intPosWhere > 0 Then
strFullString = Left(strFullString, intPosWhere - 3)
Else: strFullString = Left(strFullString, intPosSemi - 1)
End If
If frm.chkMajor And frm.lboMajor.ItemsSelected.Count Then
booFirstFlag = True
strCritString = "WHERE COPEstudentData!Major In("
strBuildString = ""
For Each intSelItem In frm.lboMajor.ItemsSelected
strBuildString = strBuildString & "," & frm.lboMajor.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString) - 1)
End If
strCritString = strCritString & strBuildString & ")"
End If
If frm.chkQuarter And frm.lboQuarter.ItemsSelected.Count Then
If booFirstFlag Then
strCritString = strCritString & " AND "
Else
strCritString = "WHERE "
booFirstFlag = True
End If
strCritString = strCritString & "COPEstudentdata!Quarter In("
strBuildString = ""
For Each intSelItem In frm.lboQuarter.ItemsSelected
strBuildString = strBuildString & "," & frm.lboQuarter.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString) - 1)
End If
strCritString = strCritString & strBuildString & ")"
End If
If frm.chkClassLevel And frm.lboClassLevel.ItemsSelected.Count Then
If booFirstFlag Then
strCritString = strCritString & " AND "
Else
strCritString = "WHERE "
booFirstFlag = True
End If
strCritString = strCritString & "COPEstudentdata![Class Level] In("
strBuildString = ""
For Each intSelItem In frm.lboClassLevel.ItemsSelected
strBuildString = strBuildString & "," & frm.lboClassLevel.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString) - 1)
End If
strCritString = strCritString & strBuildString & ")"
End If
If frm.chkClassEnrolled And frm.lboClassEnrolled.ItemsSelected.Count Then
If booFirstFlag Then
strCritString = strCritString & " AND "
Else
strCritString = "WHERE "
booFirstFlag = True
End If
strCritString = strCritString & "COPEstudentdata![Class Enrolled]="
strBuildString = ""
For Each intSelItem In frm.lboClassEnrolled.ItemsSelected
strBuildString = strBuildString & "," & frm.lboClassEnrolled.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString) - 1)
End If
strCritString = strCritString & strBuildString & ")"
End If
If frm.chkProgramofInterest And frm.lboProgramofInterest.ItemsSelected.Count Then
If booFirstFlag Then
strCritString = strCritString & " AND "
Else
strCritString = "WHERE "
booFirstFlag = True
End If
strCritString = strCritString & "COPEstudentdata![Program of Interest] In("
strBuildString = ""
For Each intSelItem In frm.lboProgramofInterest.ItemsSelected
strBuildString = strBuildString & "," & frm.lboProgramofInterest.ItemData(intSelItem)
Next intSelItem
If strBuildString <> "" Then
strBuildString = Right(strBuildString, Len(strBuildString) - 1)
End If
strCritString = strCritString & strBuildString & ")"
End If
strFullString = strFullString & vbCrLf & strCritString
qd.SQL = strFullString
Set rst = CurrentDb.OpenRecordset("qryStudInfo")
If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Exit Sub
End If
rst.Close
DoCmd.OpenQuery ("qryStudInfo")
Forms("StudInfoQuery").Refresh
Set rst = Nothing
Set qd = Nothing
End Sub