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!

Register Log in
  • Congratulations Chris Miller 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

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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Thanks for your help [smile]
I get an
Compile Error: said:
Expected: end of statement.
Click to expand...
I tried the below, but it still gives the Error:
Error: said:
Syntax error
Click to expand...
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
 
Upvote 0 Downvote
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
Click to expand...

Thanks again for all your help [smile]

Thank you,

Kind regards

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

Similar threads

TrekBiker
  • Locked
  • Question
My brewery client normally uses b 1
Replies
6
Views
340
strongm
strongm
Kennelbloke
  • Solved
Subform not updating in parent form
Replies
10
Views
376
Kennelbloke
Kennelbloke
PrimoSteve
  • Locked
  • Question
Fill in 2nd Textbox2 from Textbox1
Replies
3
Views
334
PrimoSteve
PrimoSteve
TrekBiker
  • Locked
  • Question
Running query with parameter from Inputbox 2
Replies
8
Views
398
TrekBiker
TrekBiker
BJZeak
  • Locked
  • Question
Struggling with finding an answer to a simple question
Replies
5
Views
360
BJZeak
BJZeak

Part and Inventory Search

Sponsor

Back
Top