R
rmercado
Please check my code. I cannot get the calculated string.
Code:::
sOwner = IIf(chkOwner <> 0, "OxtnSQL", Null)
sProject = IIf(chkProject <> 0, "PxtnSQL", Null)
sResp = IIf(chkResp <> 0, "RxtnSQL", Null)
sStatus = IIf(chkStatus <> 0, "SxtnSQL", Null)
sDate = IIf(chkDate <> 0, "DxtnSQL", Null)
sStage = IIf(chkDate <> 0, "GxtnSQL", Null)
xSelected = sOwner & sProject & sResp & sStage & sStatus & sDate
Set OctlSource = Me.cmbOwn
Set PctlSource = Me.cmbProject
Set RctlSource = Me.cmbResp
Set GctlSource = Me.cmbStage
'---------------------------------------- look through cmbOwn (List Box)
For OintCurrentRow = 0 To OctlSource.ListCount - 1
If OctlSource.Selected(OintCurrentRow) Then
OstrItem1 = OstrItem1 & "'" & OctlSource.Column(0, OintCurrentRow) & "',"
End If
Next OintCurrentRow
Set OctlSource = Nothing
If OstrItem1 <> "" Then OxtnSQL = "(((problemmaster.PMEUSR) in (" &
Left(OstrItem1, Len(OstrItem1) - 1) & "))"
'---------------------------------------- look through cmbProject (List Box)
For PintCurrentRow = 0 To PctlSource.ListCount - 1
If PctlSource.Selected(PintCurrentRow) Then
PstrItem1 = PstrItem1 & "'" & PctlSource.Column(0, PintCurrentRow) & "',"
End If
Next PintCurrentRow
Set PctlSource = Nothing
If PstrItem1 <> "" Then PxtnSQL = "(((problemmaster.PMPROJ) in (" &
Left(PstrItem1, Len(PstrItem1) - 1) & "))"
'---------------------------------------- look through cmbResp (List Box)
For RintCurrentRow = 0 To RctlSource.ListCount - 1
If RctlSource.Selected(RintCurrentRow) Then
RstrItem1 = RstrItem1 & "'" & RctlSource.Column(0, RintCurrentRow) & "',"
End If
Next RintCurrentRow
Set RctlSource = Nothing
If RstrItem1 <> "" Then RxtnSQL = "(((problemmaster.PMASYS) in (" &
Left(RstrItem1, Len(RstrItem1) - 1) & "))"
'---------------------------------------- look through cmbStage (List Box)
For GintCurrentRow = 0 To GctlSource.ListCount - 1
If GctlSource.Selected(GintCurrentRow) Then
GstrItem1 = GstrItem1 & "'" & GctlSource.Column(0, GintCurrentRow) & "',"
End If
Next GintCurrentRow
Set GctlSource = Nothing
If GstrItem1 <> "" Then GxtnSQL = "(((problemmaster.PMAASN) in (" &
Left(GstrItem1, Len(GstrItem1) - 1) & "))"
'========================================================================================================
xInt = 1
xtnSQL = ""
xOpt = Nz(Len((xSelected)), 0)
xOpt = (xOpt / 7)
xLoc = 1
For i = xInt To xOpt
xCompSQL = Mid(xSelected, xLoc, 7)
xtnSQL = xtnSQL & " " & Mid(xSelected, xLoc, 7) & IIf(xInt = xOpt, "", " " &
Chr(38) & " " & Chr(34) & " And " & Chr(34) & " " & Chr(38))
xLoc = xLoc + 7
xInt = xInt + 1
Next i
'========================================================================================================
xtnSQL = "WHERE & " & xtnSQL & " & ORDER BY problemmaster.PMASYS,
problemmaster.PMSTAT DESC;"
sourceSQL = "SELECT problemmaster.PMSEQ, problemmaster.PMSDES,
problemmaster.PMEUSR, problemmaster.PMLAB,
IIf(IsNumeric([BusPri])=True,CDbl([BusPri]),Null) AS Bus_Pri,
problemmaster.PMASYS, statuscodes.TTSDES, faulthistory.FaultRem,
problemmaster.PMSTAT, problemmaster.PMAASN,, problemmaster.PMPROJ,
problemmaster.PMEDAT " & _
"FROM ((problemmaster LEFT JOIN Latest_Trans ON problemmaster.PMSEQ =
Latest_Trans.FaultNo) LEFT JOIN faulthistory ON (Latest_Trans.FaultNo =
faulthistory.FaultNo) AND (Latest_Trans.MaxOfStartDate =
faulthistory.StartDate)) LEFT JOIN statuscodes ON problemmaster.PMSTAT =
statuscodes.TTSTAT " & xtnSQL
Problem:::
I cannot get the correct string value of :
OxtnSQL
PxtnSQL
RxtnSQL
GxtnSQL
Thanks,
Code:::
sOwner = IIf(chkOwner <> 0, "OxtnSQL", Null)
sProject = IIf(chkProject <> 0, "PxtnSQL", Null)
sResp = IIf(chkResp <> 0, "RxtnSQL", Null)
sStatus = IIf(chkStatus <> 0, "SxtnSQL", Null)
sDate = IIf(chkDate <> 0, "DxtnSQL", Null)
sStage = IIf(chkDate <> 0, "GxtnSQL", Null)
xSelected = sOwner & sProject & sResp & sStage & sStatus & sDate
Set OctlSource = Me.cmbOwn
Set PctlSource = Me.cmbProject
Set RctlSource = Me.cmbResp
Set GctlSource = Me.cmbStage
'---------------------------------------- look through cmbOwn (List Box)
For OintCurrentRow = 0 To OctlSource.ListCount - 1
If OctlSource.Selected(OintCurrentRow) Then
OstrItem1 = OstrItem1 & "'" & OctlSource.Column(0, OintCurrentRow) & "',"
End If
Next OintCurrentRow
Set OctlSource = Nothing
If OstrItem1 <> "" Then OxtnSQL = "(((problemmaster.PMEUSR) in (" &
Left(OstrItem1, Len(OstrItem1) - 1) & "))"
'---------------------------------------- look through cmbProject (List Box)
For PintCurrentRow = 0 To PctlSource.ListCount - 1
If PctlSource.Selected(PintCurrentRow) Then
PstrItem1 = PstrItem1 & "'" & PctlSource.Column(0, PintCurrentRow) & "',"
End If
Next PintCurrentRow
Set PctlSource = Nothing
If PstrItem1 <> "" Then PxtnSQL = "(((problemmaster.PMPROJ) in (" &
Left(PstrItem1, Len(PstrItem1) - 1) & "))"
'---------------------------------------- look through cmbResp (List Box)
For RintCurrentRow = 0 To RctlSource.ListCount - 1
If RctlSource.Selected(RintCurrentRow) Then
RstrItem1 = RstrItem1 & "'" & RctlSource.Column(0, RintCurrentRow) & "',"
End If
Next RintCurrentRow
Set RctlSource = Nothing
If RstrItem1 <> "" Then RxtnSQL = "(((problemmaster.PMASYS) in (" &
Left(RstrItem1, Len(RstrItem1) - 1) & "))"
'---------------------------------------- look through cmbStage (List Box)
For GintCurrentRow = 0 To GctlSource.ListCount - 1
If GctlSource.Selected(GintCurrentRow) Then
GstrItem1 = GstrItem1 & "'" & GctlSource.Column(0, GintCurrentRow) & "',"
End If
Next GintCurrentRow
Set GctlSource = Nothing
If GstrItem1 <> "" Then GxtnSQL = "(((problemmaster.PMAASN) in (" &
Left(GstrItem1, Len(GstrItem1) - 1) & "))"
'========================================================================================================
xInt = 1
xtnSQL = ""
xOpt = Nz(Len((xSelected)), 0)
xOpt = (xOpt / 7)
xLoc = 1
For i = xInt To xOpt
xCompSQL = Mid(xSelected, xLoc, 7)
xtnSQL = xtnSQL & " " & Mid(xSelected, xLoc, 7) & IIf(xInt = xOpt, "", " " &
Chr(38) & " " & Chr(34) & " And " & Chr(34) & " " & Chr(38))
xLoc = xLoc + 7
xInt = xInt + 1
Next i
'========================================================================================================
xtnSQL = "WHERE & " & xtnSQL & " & ORDER BY problemmaster.PMASYS,
problemmaster.PMSTAT DESC;"
sourceSQL = "SELECT problemmaster.PMSEQ, problemmaster.PMSDES,
problemmaster.PMEUSR, problemmaster.PMLAB,
IIf(IsNumeric([BusPri])=True,CDbl([BusPri]),Null) AS Bus_Pri,
problemmaster.PMASYS, statuscodes.TTSDES, faulthistory.FaultRem,
problemmaster.PMSTAT, problemmaster.PMAASN,, problemmaster.PMPROJ,
problemmaster.PMEDAT " & _
"FROM ((problemmaster LEFT JOIN Latest_Trans ON problemmaster.PMSEQ =
Latest_Trans.FaultNo) LEFT JOIN faulthistory ON (Latest_Trans.FaultNo =
faulthistory.FaultNo) AND (Latest_Trans.MaxOfStartDate =
faulthistory.StartDate)) LEFT JOIN statuscodes ON problemmaster.PMSTAT =
statuscodes.TTSTAT " & xtnSQL
Problem:::
I cannot get the correct string value of :
OxtnSQL
PxtnSQL
RxtnSQL
GxtnSQL
Thanks,