Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 Singlers.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
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.
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.