Hi all,
Thanks for a great forum!
I am hitting my head against a wall here, staring at the code, I need a fresh set of eyes.
I have the following code:
The issue stems from the If Pack <> “” Then ( after the for loop )
It seems to ignore the
I don’t understand why it is, do I need this in the for loop? ( I thought this would just repeat the statement needlessly )
And it the first iteration of the for loop ( when the pack is null ) it adds the HAVING statement there, so I thought I would not need it later, .’. just an AND needed in the last If.
Please help this is driving me mad!
I have also tried the following but get a syntax error:
Thanks for your forthcoming help
Thank you,
Kind regards
Triacona
Thanks for a great forum!
I am hitting my head against a wall here, staring at the code, I need a fresh set of eyes.
I have the following code:
Code:
[COLOR=#4E9A06]'------------------DC RUN QUERY BEGIN---------------------------------------------------------
'------------------DC RUN QUERY BEGIN---------------------------------------------------------[/color]
[COLOR=#204A87]Private Sub[/color] DcRunQuery_Click()
[COLOR=#204A87]Dim[/color] db As DAO.Database
[COLOR=#204A87]Dim[/color] qdf As DAO.QueryDef
[COLOR=#204A87]Dim[/color] SQL [COLOR=#204A87]As String[/color]
[COLOR=#204A87]Dim[/color] Lbx As ListBox, idx
[COLOR=#204A87]Dim[/color] Pack [COLOR=#204A87]As String[/color]
[COLOR=#204A87]Dim[/color] stDocName [COLOR=#204A87]As String[/color]
stDocName = ListDc.Column(2)
[COLOR=#204A87] If Not[/color] QueryExists(stDocName) [COLOR=#204A87]Then[/color]
MsgBox stDocName & " Query doesn't exist, RUN the Report!", vbExclamation, "Run The Report!!!"
[COLOR=#204A87]ElseIf[/color] txtEndDate.Enabled = [COLOR=#204A87]True Then
[/color]
Select Case stDocName
[COLOR=#4E9A06] '1st case----------------------------------------------------------------------------------------
[/color] [COLOR=#204A87]Case[/color] "DcBtwDatesVarSpecs"
[COLOR=#204A87]Set[/color] db = CurrentDb
[COLOR=#204A87]Set[/color] qdf = db.QueryDefs("DcBtwDatesVarSpecs")
[COLOR=#204A87]Set[/color] Lbx = lstbXPscodes
SQL = "SELECT " & _
"UNI7LIVE_DCAPPL.REFVAL AS Reference, UNI7LIVE_DCAPPL.DTYPNUMBCO AS PsCode, " & _
"UNI7LIVE_DCAPPL.DCAPPTYP AS ApplicationType, DcAppTyp.CODETEXT AS ApplicationTypeTxt, " & _
"UNI7LIVE_DCAPPL.DATEAPVAL AS ValidDate, UNI7LIVE_DCAPPL.DECSN AS DecisionCd, " & _
"DcDecisionCodes.CODETEXT AS Decision, UNI7LIVE_DCAPPL.DECTYPE AS DecisionType, " & _
"UNI7LIVE_DCAPPL.DCSTAT AS Status, UNI7LIVE_DCAPPL.OFFCODE, DcOffCodeList.NAME AS OfficerName, " & _
"UNI7LIVE_DCAPPL.PROPOSAL AS Proposal, Onelinereplace([ADDRESS]) AS Addr, " & _
"UNI7LIVE_DCAPPL.DATE8WEEK AS TargetDate, UNI7LIVE_DCAPPL.DATEDECISS AS DateDecIssued, " & _
"UNI7LIVE_DCAPPL.FEEREQ, UNI7LIVE_DCAPPL.PPA_FLAG AS ExtTime, " & _
"UNI7LIVE_DCAPPL.APPNAME AS Applicant, UNI7LIVE_DCAPPL.AGTNAME AS Agent " & _
"FROM " & _
"(((UNI7LIVE_DCAPPL " & _
"Left Join DcOffCodeList ON UNI7LIVE_DCAPPL.OFFCODE = DcOffCodeList.OFFCODE) " & _
"Left Join UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) " & _
"Left Join DcDecisionCodes ON UNI7LIVE_DCAPPL.DECSN = DcDecisionCodes.CODEVALUE) " & _
"Left Join DCAPPTYP ON UNI7LIVE_DCAPPL.DCAPPTYP = DCAPPTYP.CODEVALUE " & _
"GROUP BY " & _
"UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DCAPPTYP, DcAppTyp.CODETEXT, UNI7LIVE_DCAPPL.DATEAPVAL, " & _
"UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.OFFCODE, " & _
"DcOffCodeList.NAME, UNI7LIVE_DCAPPL.PROPOSAL, Onelinereplace([ADDRESS]), UNI7LIVE_DCAPPL.DATE8WEEK, " & _
"UNI7LIVE_DCAPPL.DATEDECISS, UNI7LIVE_DCAPPL.FEEREQ, UNI7LIVE_DCAPPL.PPA_FLAG, " & _
"UNI7LIVE_DCAPPL.APPNAME,UNI7LIVE_DCAPPL.AGTNAME "
[COLOR=#204A87]For Each[/color] idx [COLOR=#204A87]In[/color] Lbx.ItemsSelected
[COLOR=#204A87]If[/color] Pack <> "" [COLOR=#204A87]Then[/color]
Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "')"
[COLOR=#204A87]Else[/color]
Pack = " HAVING (((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
[COLOR=#204A87]End If[/color]
[COLOR=#204A87]Next[/color] [COLOR=#4E9A06]' end for[/color]
[COLOR=#204A87]If[/color] Pack <> "" [COLOR=#204A87]Then[/color]
Pack = Pack & " AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Between [Forms]![MainScreen].[txtStartDate] And [Forms]![MainScreen].[txtEndDate])) " & _
" ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
qdf.SQL = SQL & Pack
[COLOR=#204A87]Else[/color]
Pack = Pack & " ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
qdf.SQL = SQL & Pack
[COLOR=#204A87]End If[/color]
[COLOR=#204A87]Debug.Print[/color] SQL & Pack
DoCmd.OpenQuery stDocName, acViewNormal
DoCmd.Maximize
qdf.Close
[COLOR=#204A87]Set[/color] qdf = Nothing
[COLOR=#204A87]Set[/color] db = Nothing
[COLOR=#204A87]Set[/color] Lbx = Nothing
[COLOR=#204A87]End Select[/color]
[COLOR=#4E9A06]'end Select case----------------------------------------------------------------------------------------
[/color]
[COLOR=#204A87]ElseIf[/color] IsNull(txtStartDate And txtEndDate) [COLOR=#204A87]Then[/color]
DoCmd.RunMacro "MsgBoxNoDate"
[COLOR=#204A87]Else[/color]: DoCmd.OpenQuery stDocName, acNormal, acEdit
[COLOR=#204A87] End If[/color]
[COLOR=#204A87]End Sub[/color]
[COLOR=#4E9A06]'------------------DC RUN QUERY END-----------------------------------------------------------
'------------------DC RUN QUERY END-----------------------------------------------------------
'------------------DC RUN QUERY END-----------------------------------------------------------[/color]
The issue stems from the If Pack <> “” Then ( after the for loop )
It seems to ignore the
Code:
Pack = Pack & " AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Between [Forms]![MainScreen].[txtStartDate] And [Forms]![MainScreen].[txtEndDate])) ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
qdf.SQL = SQL & Pack
I don’t understand why it is, do I need this in the for loop? ( I thought this would just repeat the statement needlessly )
And it the first iteration of the for loop ( when the pack is null ) it adds the HAVING statement there, so I thought I would not need it later, .’. just an AND needed in the last If.
Please help this is driving me mad!
I have also tried the following but get a syntax error:
Code:
SQL = "SELECT " & _
"UNI7LIVE_DCAPPL.REFVAL AS Reference, UNI7LIVE_DCAPPL.DTYPNUMBCO AS PsCode, " & _
"UNI7LIVE_DCAPPL.DCAPPTYP AS ApplicationType, DcAppTyp.CODETEXT AS ApplicationTypeTxt, " & _
"UNI7LIVE_DCAPPL.DATEAPVAL AS ValidDate, UNI7LIVE_DCAPPL.DECSN AS DecisionCd, " & _
"DcDecisionCodes.CODETEXT AS Decision, UNI7LIVE_DCAPPL.DECTYPE AS DecisionType, " & _
"UNI7LIVE_DCAPPL.DCSTAT AS Status, UNI7LIVE_DCAPPL.OFFCODE, DcOffCodeList.NAME AS OfficerName, " & _
"UNI7LIVE_DCAPPL.PROPOSAL AS Proposal, Onelinereplace([ADDRESS]) AS Addr, " & _
"UNI7LIVE_DCAPPL.DATE8WEEK AS TargetDate, UNI7LIVE_DCAPPL.DATEDECISS AS DateDecIssued, " & _
"UNI7LIVE_DCAPPL.FEEREQ, UNI7LIVE_DCAPPL.PPA_FLAG AS ExtTime, " & _
"UNI7LIVE_DCAPPL.APPNAME AS Applicant, UNI7LIVE_DCAPPL.AGTNAME AS Agent " & _
"FROM " & _
"(((UNI7LIVE_DCAPPL " & _
"Left Join DcOffCodeList ON UNI7LIVE_DCAPPL.OFFCODE = DcOffCodeList.OFFCODE) " & _
"Left Join UNI7LIVE_CNWARD ON UNI7LIVE_DCAPPL.WARD = UNI7LIVE_CNWARD.WARD) " & _
"Left Join DcDecisionCodes ON UNI7LIVE_DCAPPL.DECSN = DcDecisionCodes.CODEVALUE) " & _
"Left Join DCAPPTYP ON UNI7LIVE_DCAPPL.DCAPPTYP = DCAPPTYP.CODEVALUE " & _
"GROUP BY " & _
"UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.DTYPNUMBCO, UNI7LIVE_DCAPPL.DCAPPTYP, DcAppTyp.CODETEXT, UNI7LIVE_DCAPPL.DATEAPVAL, " & _
"UNI7LIVE_DCAPPL.DECSN, DcDecisionCodes.CODETEXT, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.OFFCODE, " & _
"DcOffCodeList.NAME, UNI7LIVE_DCAPPL.PROPOSAL, Onelinereplace([ADDRESS]), UNI7LIVE_DCAPPL.DATE8WEEK, " & _
"UNI7LIVE_DCAPPL.DATEDECISS, UNI7LIVE_DCAPPL.FEEREQ, UNI7LIVE_DCAPPL.PPA_FLAG, " & _
"UNI7LIVE_DCAPPL.APPNAME,UNI7LIVE_DCAPPL.AGTNAME " & _
"HAVING " & _
"(((UNI7LIVE_DCAPPL.DATEAPVAL) Between [Forms]![MainScreen].[txtStartDate] AND [Forms]![MainScreen].[txtEndDate]) "
[COLOR=#204A87]For Each[/color] idx [COLOR=#204A87]In[/color] Lbx.ItemsSelected
[COLOR=#204A87]If[/color] Pack <> "" [COLOR=#204A87]Then[/color]
Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
[COLOR=#204A87]Else[/color]
Pack = " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
[COLOR=#204A87]End If[/color]
[COLOR=#204A87]Next[/color] [COLOR=#4E9A06]' end for[/color]
[COLOR=#204A87] If[/color] Pack <> "" [COLOR=#204A87]Then[/color]
Pack = Pack & " " & _
" ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
qdf.SQL = SQL & Pack
[COLOR=#204A87]Else[/color]
Pack = Pack & " ORDER BY UNI7LIVE_DCAPPL.REFVAL;" ' ampersand and underscore removed here
qdf.SQL = SQL & Pack
[COLOR=#204A87]End If[/color]
Thanks for your forthcoming help
Thank you,
Kind regards
Triacona