Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Issues With my SQL Having statement in my VBA code 1

Status
Not open for further replies.

Triacona

Technical User
Jun 11, 2009
462
GB
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:
[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 [bigsmile]



Thank you,

Kind regards

Triacona
 
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.
 
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:
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:
[COLOR=#204A87]Case[/color] "DcBtwDatesVarSpecs"
            
                    [COLOR=#204A87]Set[/color] db = CurrentDb
                    [COLOR=#204A87]Set[/color] qdf = db.QueryDefs(stDocName)
                    [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, " & _
                                "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 = " AND ((UNI7LIVE_DCAPPL.DTYPNUMBCO) LIKE '" & Lbx.Column(0, idx) & "')"
                        [COLOR=#204A87]End If[/color]
                    
                    [COLOR=#204A87]Next[/color] [COLOR=#4E9A06]' end for[/color]

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
 
I would guess you want ## around your dates in Access, so:

Code:
"HAVING " & _
  "((UNI7LIVE_DCAPPL.DATEAPVAL) Between[highlight #FCE94F] #" &[/highlight] [Forms]![MainScreen].[txtStartDate] [highlight #FCE94F]& "#[/highlight] AND[highlight #FCE94F] #" &[/highlight] [Forms]![MainScreen].[txtEndDate]) [highlight #FCE94F]& "#"
[/highlight]


---- Andy

There is a great need for a sarcasm font.
 
Thanks for your help [smile]
I get an
Compile Error: said:
Expected: end of statement.
I tried the below, but it still gives the Error:
Error: said:
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
 
This saves :
Code:
 "((UNI7LIVE_DCAPPL.DATEAPVAL) Between # & [Forms]![MainScreen].[txtStartDate] & # AND # & [Forms]![MainScreen].[txtEndDate]) & #"
But gives the following error when the event runs:
Run -time error 3075 said:
Missing ),], or item in query expression

Thanks again for all your help [smile]

Thank you,

Kind regards

Triacona
 
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:
[tt]
HAVING UNI7LIVE_DCAPPL.DATEAPVAL Between #1/1/2019# AND #2/2/2019#
[/tt]
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.
 
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
 
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)
    [blue]
Debug.Print [Forms]![MainScreen].[txtStartDate] 
Debug.Print [Forms]![MainScreen].[txtEndDate]) 
[/blue]
    If Not QueryExists(stDocName) Then
...


---- Andy

There is a great need for a sarcasm font.
 
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
 
Maybe we are trying to fix the wrong part of your SQL?

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

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.
 
I took a second look at your SQL, and you get these fields:
[tt]
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
[/tt]
and then GROUP them by:
[tt]
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, [red]
Onelinereplace([ADDRESS])[/red]
[/tt]
Your [tt]Onelinereplace([ADDRESS][/tt] 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:
[tt]
OR UNI7LIVE_DCAPPL.DTYPNUMBCO IN ('0001', '0002', '0003')
[/tt]

[/tt]



---- Andy

There is a great need for a sarcasm font.
 
Thanks for all your help on this [bigsmile][2thumbsup]

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! [2thumbsup][thanks2]

Thank you,

Kind regards

Triacona
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top