MS Access VBA to QV Module

Fri, 15 Jul 2011 20:18:33 GMT

I have the following code in MS Access VBA, and I would like to use it in QlikView. Because of the complexity of what I am trying to do, I am using recordsets in VBA. So my basic question is whether or not I can use recordsets in QlikView, and if not, then is there an alternative that would make it possible?


Sub PYCalc()
Set rs = New ADODB.Recordset
Dim sTripNo As String
Dim sPYHrs As Single
Dim sHrsDiff As Single
Dim sMgn As Single
Dim sPY As Single
rs.Open "SELECT PM.[PMORD#], PM.PMMRGA, PM.PMTRLRHRS, RD.RLRLDHRS, PV.MDLEVEL, PV.MDHOURS, PV.MDMARGIN, " & _
"FROM (ILFILE_PROFMAST PM INNER JOIN ILFILE_RLDDELAY RD ON PM.PMINAR = RD.RLARA) INNER JOIN ILFILE_PODVALUE PV ON " & _
"PM.PMINAR = PV.MDARA", cn, adOpenDynamic, adLockOptimistic

With rs
    .MoveFirst
    Do Until .EOF
    sTripNo = .Fields(0).Value
    'If data entry error makes margin > $99,999 then set it to $100
    If rs.Fields(1) > 99999 Then
        Set rs.Fields(1).Value = 100
        Debug.Print rs.Fields(0)
    End If
    'Level 1
    If rs.Fields(4).Value = 1 Then
        sPYHrs = (rs.Fields(2).Value + rs.Fields(3).Value + rs.Fields(5).Value)
        sMgn = rs.Fields(1).Value + rs.Fields(6).Value
        .MoveNext
    Else
        If sPYHrs + rs.Fields(5).Value < 318 Then
            sPYHrs = sPYHrs + rs.Fields(5).Value
            sMgn = sMgn + rs.Fields(6).Value
            .MoveNext
        Else
            sHrsDiff = sPYHrs + rs.Fields(5).Value - iPYHrsLimit
            sMgn = sMgn + (rs.Fields(6).Value * (1 - (sHrsDiff / rs.Fields(5).Value)))
            sPY = Round(sMgn, 2)
            cn.Execute "UPDATE tblPYCalc SET PYCalc = " & sPY & " WHERE [PMORD#]= '" & sTripNo & "'"
            Do Until rs.Fields(0).Value <> sTripNo
                    .MoveNext
                    If .EOF Then
                    Exit Do
                    End If
            Loop
        End If
        
    End If
    
    Loop
    
    .Close
End With
End Sub

Re: MS Access VBA to QV Module

Mon, 23 Jan 2012 16:16:37 GMT

Maybe no one else has had this problem, but just in case here is what I ended up doing:

I created a table from the recordset, then used a macro to run the loops and ifs to calculate the values I needed. It ended up being pretty complex, but it works now.

topic Re: MS Access VBA to QV Module in QlikView

MS Access VBA to QV Module

Re: MS Access VBA to QV Module