×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Issues With my SQL Having statement in my VBA code

Issues With my SQL Having statement in my VBA code

Issues With my SQL Having statement in my VBA code

(OP)
Hi all,
Thanks for a great forum! smile
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

'------------------DC RUN QUERY BEGIN---------------------------------------------------------
'------------------DC RUN QUERY BEGIN---------------------------------------------------------
Private Sub DcRunQuery_Click()
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim SQL As String
    Dim Lbx As ListBox, idx
    Dim Pack As String
    Dim stDocName As String
        stDocName = ListDc.Column(2)
    
    
    If Not QueryExists(stDocName) Then
        MsgBox stDocName & " Query doesn't exist, RUN the Report!", vbExclamation, "Run The Report!!!"
        
        ElseIf txtEndDate.Enabled = True Then
        
            Select Case stDocName
                '1st case----------------------------------------------------------------------------------------
                Case "DcBtwDatesVarSpecs"
            
                    Set db = CurrentDb
                    Set qdf = db.QueryDefs("DcBtwDatesVarSpecs")
                    Set 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 "
             
                    For Each idx In Lbx.ItemsSelected
                    
                        If Pack <> "" Then
                            Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "')"
                        Else
                            Pack = " HAVING (((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
                        End If
                    
                    Next ' end for

                    If Pack <> "" Then
                        Pack = Pack & " AND ((UNI7LIVE_DCAPPL.DATEAPVAL) Between [Forms]![MainScreen].[txtStartDate] And [Forms]![MainScreen].[txtEndDate])) " & _
                        " ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
                        qdf.SQL = SQL & Pack
                    Else
                        Pack = Pack & " ORDER BY UNI7LIVE_DCAPPL.REFVAL;" 
                        qdf.SQL = SQL & Pack
                    End If
            
                    Debug.Print SQL & Pack
            
                    DoCmd.OpenQuery stDocName, acViewNormal
                    DoCmd.Maximize
                  
                    qdf.Close
               
                    Set qdf = Nothing
                    Set db = Nothing
                    Set Lbx = Nothing
            End Select
        'end Select case----------------------------------------------------------------------------------------
        
        ElseIf IsNull(txtStartDate And txtEndDate) Then
            DoCmd.RunMacro "MsgBoxNoDate"
        Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    End If

End Sub
'------------------DC RUN QUERY END-----------------------------------------------------------
'------------------DC RUN QUERY END-----------------------------------------------------------
'------------------DC RUN QUERY END----------------------------------------------------------- 

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]) "


             
                    For Each idx In Lbx.ItemsSelected
                    
                        If Pack <> "" Then
                            Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
                        Else
                            Pack = " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') "
                        End If
                    
                    Next ' end for

                    If Pack <> "" Then
                        Pack = Pack & "  " & _
                        " ORDER BY UNI7LIVE_DCAPPL.REFVAL;"
                        qdf.SQL = SQL & Pack
                    Else
                        Pack = Pack & " ORDER BY UNI7LIVE_DCAPPL.REFVAL;" ' ampersand and underscore removed here
                        qdf.SQL = SQL & Pack
                    End If 


Thanks for your forthcoming help bigsmile

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

I don't want to insult anybody here, but... did you step thru your code to see what's going on, where your code is going, which parts are executed, which parts are omitted, etc....? ponder


---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

(OP)
Hi Andrzejek,

Thanks for replying smile

I have stepped through the code and the between dates runs... see the output SQL and intermediate window SQL here:

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, 
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, 
UNI7LIVE_DCAPPL.DATE8WEEK, 
UNI7LIVE_DCAPPL.DATEDECISS, 
UNI7LIVE_DCAPPL.FEEREQ, 
UNI7LIVE_DCAPPL.PPA_FLAG, 
UNI7LIVE_DCAPPL.APPNAME, 
UNI7LIVE_DCAPPL.AGTNAME, 
Onelinereplace([ADDRESS])
HAVING 
(
((UNI7LIVE_DCAPPL.DATEAPVAL) Between [Forms]![MainScreen].[txtStartDate] And [Forms]![MainScreen].[txtEndDate])) 
OR (((UNI7LIVE_DCAPPL.DTYPNUMBCO) Like '0001')) 
OR (((UNI7LIVE_DCAPPL.DTYPNUMBCO) Like '0002')) 
OR (((UNI7LIVE_DCAPPL.DTYPNUMBCO) Like '0003'))
ORDER BY 
UNI7LIVE_DCAPPL.REFVAL; 
After that I amended the following to include an AND instead of an OR:

CODE --> VBA

Case "DcBtwDatesVarSpecs"
            
                    Set db = CurrentDb
                    Set qdf = db.QueryDefs(stDocName)
                    Set 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, " & _
                                "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]) "


                        For Each idx In Lbx.ItemsSelected
                    
                        If Pack <> "" Then
                            Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "')"
                        Else
                            Pack = " AND ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "')"
                        End If
                    
                    Next ' end for 

None of the above works to include the between dates statement, I have no idea why as the outputted SQL above clearly has it in the statement.
Please help this is really maddening.
Thanks 2thumbsup

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

I would guess you want ## around your dates in Access, so:

CODE

"HAVING " & _
  "((UNI7LIVE_DCAPPL.DATEAPVAL) Between #" & [Forms]![MainScreen].[txtStartDate] & "# AND #" & [Forms]![MainScreen].[txtEndDate]) & "#"
 


---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

(OP)
Thanks for your help smile
I get an

Quote (Compile Error:)


Expected: end of statement.
I tried the below, but it still gives the Error:

Quote (Error:)


Syntax error

CODE

"HAVING " & _
  "((UNI7LIVE_DCAPPL.DATEAPVAL) Between "#" & [Forms]![MainScreen].[txtStartDate] & "#" AND "#" & [Forms]![MainScreen].[txtEndDate] & "#" )" 

Any more help would be greatly appreciated thumbsup

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

(OP)
This saves :

CODE

"((UNI7LIVE_DCAPPL.DATEAPVAL) Between # & [Forms]![MainScreen].[txtStartDate] & # AND # & [Forms]![MainScreen].[txtEndDate]) & #" 
But gives the following error when the event runs:

Quote (Run -time error 3075)


Missing ),], or item in query expression

Thanks again for all your help smile

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

There are some ( and ) that do not match.
Try:

CODE

"HAVING " & _
  "UNI7LIVE_DCAPPL.DATEAPVAL Between #" & [Forms]![MainScreen].[txtStartDate] & "# AND #" & [Forms]![MainScreen].[txtEndDate]) & "#" 

You should end up with something like:

HAVING UNI7LIVE_DCAPPL.DATEAPVAL Between #1/1/2019# AND #2/2/2019#

depending on the dates on your Form.

That is assuming your DATEAPVAL is defined as DATE in your data base.

---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

(OP)
Dear Andy,
Thanks for your help on this, your code gives me the Expected: end of statement error again.
Just to note the:
[Forms]![MainScreen].[txtStartDate]
[Forms]![MainScreen].[txtEndDate])
are date controls on my MainScreen form, so they may not behave like a string...I think, I'm not sure anymore, this is driving me to lunacy...

Thanks again.

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

Try this at the top of your code to see what you have in those two fields:

CODE

....
    Dim stDocName As String
        stDocName = ListDc.Column(2)
    
Debug.Print [Forms]![MainScreen].[txtStartDate] 
Debug.Print [Forms]![MainScreen].[txtEndDate]) 

    If Not QueryExists(stDocName) Then
... 


---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

(OP)
Dear Andy,
The return is thus:
01/01/2019
31/01/2019
Is it to do with the formatting?
Thanks again for all your help smile

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

Maybe we are trying to fix the wrong part of your SQL?

Try:
SELECT DATEAPVAL
FROM UNI7LIVE_DCAPPL
WHERE DATEAPVAL Between #01/01/2019# AND #31/01/2019#


and see if this will give you any valid data
DATEAPVAL is defined as DATE, right?


---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

I took a second look at your SQL, and you get these fields:

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,
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

and then GROUP them 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,
UNI7LIVE_DCAPPL.DATE8WEEK,
UNI7LIVE_DCAPPL.DATEDECISS,
UNI7LIVE_DCAPPL.FEEREQ,
UNI7LIVE_DCAPPL.PPA_FLAG,
UNI7LIVE_DCAPPL.APPNAME,
UNI7LIVE_DCAPPL.AGTNAME,
Onelinereplace([ADDRESS])


Your Onelinereplace([ADDRESS] in GROUP BY part is not part of your SELECT part.
So the question is: do you really need to GROUP BY in your Select statement, and HAVING just simple becomes WHERE part of the statement. You don't have any MIN(), MAX, SUM(), etc. to use the GROUP on...

And you use LIKE, but no wild card. Do you want to have just:

OR UNI7LIVE_DCAPPL.DTYPNUMBCO IN ('0001', '0002', '0003')


[/tt]


---- Andy

There is a great need for a sarcasm font.

RE: Issues With my SQL Having statement in my VBA code

(OP)
Thanks for all your help on this bigsmile2thumbsup

I think I have solved it, also removing the GROUPBY and adding the Between dates in the for loop as such:

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, " & _
                                "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 "


                    For Each idx In Lbx.ItemsSelected
                    
                        If Pack <> "" Then
                            Pack = Pack & " OR ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') AND UNI7LIVE_DCAPPL.DATEAPVAL Between [Forms]![MainScreen].[txtStartDate] AND [Forms]![MainScreen].[txtEndDate]"
                        Else
                            Pack = " WHERE ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "') AND UNI7LIVE_DCAPPL.DATEAPVAL Between [Forms]![MainScreen].[txtStartDate] AND [Forms]![MainScreen].[txtEndDate]"
                        End If
                    
                    Next ' end for 

In terms of the In Statement

Can an In statement be added to using the For loop?
So for example:

CODE

For Each idx In Lbx.ItemsSelected
                    
                        If Pack <> "" Then
                            Pack = Pack & "'" & Lbx.Column(0, idx) & "')"
                        Else
                            Pack = " (UNI7LIVE_DCAPPL.DTYPNUMBCO)In('" & Lbx.Column(0, idx) & "', ))"
                        End If
                    
                    Next ' end for 

Thanks again so much for your help Star coming your way! 2thumbsupthanks2

Thank you,

Kind regards

Triacona

RE: Issues With my SQL Having statement in my VBA code

How about something like this:

CODE

...
Dim strIN As String
...
For Each idx In Lbx.ItemsSelected
    If strIN = "" Then
        strIN = "'" & Lbx.Column(0, idx) & "'"
    Else
        strIN = strIN & ", '" & Lbx.Column(0, idx) & "'"
    End If
Next ' end for

If strIN <> "" Then
    Pack = " UNI7LIVE_DCAPPL.DTYPNUMBCO IN (" & strIN & ")" 
End If
... 


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close