Results 1 to 2 of 2
  1. #1
    cdpeck is offline Novice
    Windows 2K Access 2003
    Join Date
    Sep 2009
    Posts
    1

    Problem with building SQL string (VBA)

    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

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Two questions that spring to mind

    1. why are you using vbCrLf in your sql string?
    2. what does strFullString look like if you Debug.Print it?

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. replace characters in a string
    By blazixinfo@yahoo.com in forum Access
    Replies: 6
    Last Post: 08-06-2009, 03:36 PM
  2. Building a Difficult DateDiff Expression
    By jma108 in forum Queries
    Replies: 0
    Last Post: 06-15-2009, 12:39 PM
  3. order by - string and numeric
    By pen in forum Queries
    Replies: 10
    Last Post: 05-20-2009, 06:29 AM
  4. building a distribution package
    By BevA in forum Access
    Replies: 0
    Last Post: 05-26-2006, 07:04 AM
  5. Replies: 0
    Last Post: 12-05-2005, 04:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums