Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: SQL Pass Threw in VBA Code

    Has anyone ever writen an sql pass threw query in vba code?
    If so Id love to see an example
    Jim

  2. #2
    Join Date
    Dec 2002
    Posts
    3

    Re: SQL Pass Threw in VBA Code

    Originally posted by JDionne
    Has anyone ever writen an sql pass threw query in vba code?
    If so Id love to see an example
    Jim
    Here's some that passed a SQL statement to a report:

    Code:
    Sub DetermineFilterAndSort()
        
        
        Dim sSql As String
        
    
        
        Setgdb
        Set qRpt = gdb.QueryDefs("GenericIncidents")
        
        sWhere = ""
        '===============================================
        If IsNull(Me.cboLocation.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and Location='" & Format(Me.cboLocation.Value, "0000") & "' "
            Else
                sWhere = "Location='" & Format(Me.cboLocation.Value, "0000") & "' "
            End If
        End If
            
        '===============================================
        If IsNull(Me.cboWorkGroup.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and WorkGroup='" & Me.cboWorkGroup.Value & "'"
            Else
                sWhere = "WorkGroup='" & Me.cboWorkGroup.Value & "'"
            End If
        End If
    
        '===============================================
        If IsNull(Me.cboPayType.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and PayType='" & Me.cboPayType.Value & "'"
            Else
                sWhere = "PayType='" & Me.cboPayType.Value & "'"
            End If
        End If
    
        '===============================================
        If IsNull(Me.cboSourceType.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and SourceType='" & Me.cboSourceType.Value & "'"
            Else
                sWhere = "SourceType='" & Me.cboSourceType.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboReceivedBy.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and ReceivedBy='" & Me.cboReceivedBy.Value & "'"
            Else
                sWhere = "ReceivedBy='" & Me.cboReceivedBy.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboStatus.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and Status='" & Me.cboStatus.Value & "'"
            Else
                sWhere = "Status='" & Me.cboStatus.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboReportedBy.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and ReportedBy='" & Me.cboReportedBy.Value & "'"
            Else
                sWhere = "ReportedBy='" & Me.cboReportedBy.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboCategory.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and Category='" & Me.cboCategory.Value & "'"
            Else
                sWhere = "Category='" & Me.cboCategory.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboIncidentType.Value) Then
            'continue
        Else
            If Len(sWhere) > 0 Then
                sWhere = sWhere & " and IncidentType='" & Me.cboIncidentType.Value & "'"
            Else
                sWhere = "IncidentType='" & Me.cboIncidentType.Value & "'"
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboFirstDate.Value) Then
            'continue
        Else
            If IsDate(Me.cboFirstDate.Value) Then
                If Len(sWhere) > 0 Then
                    sWhere = sWhere & " and DateAdded>=#" & Me.cboFirstDate.Value & "#"
                Else
                    sWhere = "DateAdded>=#" & Me.cboFirstDate.Value & "#"
                End If
            End If
        End If
        
        '===============================================
        If IsNull(Me.cboLastDate.Value) Then
            'continue
        Else
            If IsDate(Me.cboFirstDate.Value) Then
                If Len(sWhere) > 0 Then
                    sWhere = sWhere & " and DateAdded<=#" & Me.cboLastDate.Value & "#"
                Else
                    sWhere = "DateAdded<=#" & Me.cboLastDate.Value & "#"
                End If
            End If
        End If
        
        
        sOrderBy = ""
        
        Select Case Me.frmSort1.Value
            Case 1
                sOrderBy = sOrderBy & "Workgroup, "
            Case 2
                sOrderBy = sOrderBy & "Paytype, "
            Case 3
                sOrderBy = sOrderBy & "Location, "
            Case 4
                sOrderBy = sOrderBy & "SourceType, "
            Case 5
                sOrderBy = sOrderBy & "ReceivedBy, "
            Case 6
                sOrderBy = sOrderBy & "Status, "
            Case 7
                sOrderBy = sOrderBy & "ReportedBy, "
            Case 8
                sOrderBy = sOrderBy & "Category, "
            Case 9
                sOrderBy = sOrderBy & "IncidentType, "
        End Select
        
        
        Select Case Me.frmSort2.Value
            Case 1
                sOrderBy = sOrderBy & "Workgroup, "
            Case 2
                sOrderBy = sOrderBy & "Paytype, "
            Case 3
                sOrderBy = sOrderBy & "Location, "
            Case 4
                sOrderBy = sOrderBy & "SourceType, "
            Case 5
                sOrderBy = sOrderBy & "ReceivedBy, "
            Case 6
                sOrderBy = sOrderBy & "Status, "
            Case 7
                sOrderBy = sOrderBy & "ReportedBy, "
            Case 8
                sOrderBy = sOrderBy & "Category, "
            Case 9
                sOrderBy = sOrderBy & "IncidentType, "
        End Select
        
        
        Select Case Me.frmSort3.Value
            Case 1
                sOrderBy = sOrderBy & "Workgroup, "
            Case 2
                sOrderBy = sOrderBy & "Paytype, "
            Case 3
                sOrderBy = sOrderBy & "Location, "
            Case 4
                sOrderBy = sOrderBy & "SourceType, "
            Case 5
                sOrderBy = sOrderBy & "ReceivedBy, "
            Case 6
                sOrderBy = sOrderBy & "Status, "
            Case 7
                sOrderBy = sOrderBy & "ReportedBy, "
            Case 8
                sOrderBy = sOrderBy & "Category, "
            Case 9
                sOrderBy = sOrderBy & "IncidentType, "
        End Select
        
        sOrderBy = sOrderBy & "DateAdded"
        '===============================================
        sSql = "SELECT * "
        sSql = sSql & "FROM Incidents "
        
        If Len(sWhere) > 0 Then
            sSql = sSql & " Where " & sWhere
        End If
        
        sSql = sSql & " order by " & sOrderBy
        
        qRpt.SQL = sSql
        
        gsWhere = ""
        
        If Len(sWhere) > 0 Then
            gsWhere = sWhere & vbCrLf & "order by " & sOrderBy
        Else
            gsWhere = "Order by " & sOrderBy
        End If
    
    End Sub
    JDS

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thanx for the help man
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •