Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Need help with "strCriteria" and HAVING clause

    Experts:


    In a previous posting (https://www.accessforums.net/showthr...t=77996&page=2), I obtained assistance with extracting a listbox value in forcing it to be the criteria for a query/report.

    At this time, I'd like to ask a follow-up question. However, given the length of the previous post, I wanted to start a new thread.


    ************************


    Ultimately, the listbox-value-to-report process works great!! I would like to thank everyone for the provided support thus far.

    All questions in the post thus far, however, where to re-generated a query with a **SINGLE STRING IN THE HAVING** line.


    Now, I'm now mimicking the process I previously used, but in this case, certain fields have the "Is Null" as a criteria. When I now execute my query, I don't get any errors; however, the actual string (e.g., "Maritime Intelligence Operations Center") is NOT stored in the field [CFT_Description]. Instead, the QDF creates a query where the string an **EXPRESSION** field is added.

    Please see attached images:
    1. "Current_Process_OneCriteria" -- this VBA/SQL works great given that I only have one criteria
    2. "Current_Process_MultipleCriteria" -- here, my VBA/SQL does NOT work given that I have the criteria + multiple "Is Null" in my HAVING clause


    My question:
    Do both the "strCriteria = T11_CrossFunctionalTeam..." AND "HAVING" clause in the strSQL have to be modified in order to push the "Maritime Intelligence Operations Center" into the [CFT_Description] field in the query (vs. creating the expression)? If so, what's the proper syntax for either or both of them?


    Thank you,
    EEH


    Below is the working SQL with one criteria in the HAVING clause (based on "grouping"):

    Code:
    strCriteria = "T11_CrossFunctionalTeam.CFT_Owner = '" & Me!lstCFTOwners.ItemData(varItem) & "'"
    "" & "HAVING " & strCriteria & " " & _
    "ORDER BY T11_CrossFunctionalTeam.CFT_CategorySortOrder;"


    Next is the SQL which does NOT work given multiple criteria in the HAVING clause:
    Code:
    strCriteria = "T11_CrossFunctionalTeam.CFT_Description = '" & Me!lstMET_CFTs.ItemData(varItem) & "'"
    
    "HAVING(((T96_METS.MET_Type) <> '[MET_Type -- TO BE DETERMINED]') And ((T11_CrossFunctionalTeam.CFT) Is Not Null) " & _
    "And ((T11_CrossFunctionalTeam.CFT_Description) = " & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _
    "ORDER BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
    Attached Thumbnails Attached Thumbnails Current_Process_OneCriteria.JPG   Current_Process_MultipleCriteria.JPG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    2 issues I see right off. The line building strCriteria will keep overwriting itself if more than one item is selected. Second, you're including the field name when building strCriteria and then repeating it when you add strCriteria to the SQL string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Pbaldy -- thank you for chiming in.
    Since I did NOT included the full VBA function, I can see why you made this comment.
    Ultimately though, whether I select one or multiple values from the listbox, the query generation and report output are in a loop. The process with only one criteria in the HAVING clause works great and does NOT
    need modification.
    Back to the existing problem... based on what I learned over this past weekend, all of the hours and hours of development came ultimately down to 2 things:
    1. strCriteria line
    2. HAVING clause
    As my strCriteria is NOT changing based on the previous process, I am highly confident that I merely made a mistake of **integrating** the strCriteria properly into my having clause. Maybe I have an ampersand placed
    incorrectly or I have too many (or not enough) single quotes or double-quotes.
    If you could have another look at the HAVING syntax, I'd greatly appreciate!
    Thank you,
    EEH

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I believe the picture proves my point. Your "Expression" field is essentially "DescriptionField = DescriptionField". That happens because you include the field in strCriteria and again when you incorporate that into the string. In other words, strCriteria contains (I'm paraphrasing) "Description = 'Maritime'" and then you incorporate it:

    "...AND Description = " & strCriteria

    That's going to leave you with

    "...AND Description = Description = 'Maritime'"

    If you did a debug.print on strSQL I suspect you'd see that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Paul:

    I don't doubt what you're saying... I simply believe we are focusing on different issues.

    I'm trying to figure out how to properly incorpoate the strCriteria into the strSQL. For testing purposes, I temporarily removed the IsNull and other criteria and replaced the existing HAVING statement with the folliwing:

    [CODE]
    "" & "HAVING " & strCriteria & " " & _
    [CODE]

    Now, the query works fine and all reports are outputted correctly.

    However, leaving the testing mode, I have to add the other criteria back into the HAVING clause in order to obtain the correct number of records. Attached images shall further provide the "working" and "not working" process.

    Again, how do I rewrite my HAVING clause so that that the other three (3) included but field CFT_Description receives its value from the listbox?

    Code:
    HAVING (((T96_METS.MET_Type)<>"[MET_Type -- TO BE DETERMINED]") AND ((T11_CrossFunctionalTeam.CFT) Is Not Null) AND ((T11_CrossFunctionalTeam.CFT_Description)="Commander's Assessment Board") AND ((T01_Billets.TFFMS_Last_Update) Is Not Null))
    Thanks,
    Tom
    Attached Thumbnails Attached Thumbnails Works Great.JPG   Does Not Work.JPG  

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Instead of

    "...AND Description = " & strCriteria

    you'd have

    "...AND " & strCriteria
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, this is the exact same problem discussed here:

    https://www.accessforums.net/showthread.php?t=78000

    first mentioned in post 8.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    development came ultimately down to 2 things:
    1. strCriteria line
    2. HAVING clause

    2. "Current_Process_MultipleCriteria" -- here, my VBA/SQL does NOT work given that I have the criteria + multiple "Is Null" in my HAVING clause
    and as also previously highlighted in your recent threads - use WHERE rather than HAVING if you are not grouping on a field - and as a mute point, use SELECT DISTINCT rather than GROUP BY when you are not aggregating - it is much simpler

    for example

    (T01_Billets.TFFMS_Last_Update) Is Not Null


    T01_Billets.TFFMS_Last_Update is not selected or grouped on

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Paul... I don't disagree. So, how should I fix the strCriteria then?

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see post 6?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Ajax -- even with 'DISTINCT' (and the removal of the GROUP BY), my original problem still has not been addressed.

    Code:
    SELECT DISTINCT T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder, T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T11_CrossFunctionalTeam.CFT_Owner
    FROM T11_CrossFunctionalTeam RIGHT JOIN ((T01_Billets LEFT JOIN T96_METS ON T01_Billets.aa_CSS = T96_METS.Core_CSS) LEFT JOIN T00_JunctionTable_BCFT ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk
    WHERE (((T96_METS.MET_Type)<>"[MET_Type -- TO BE DETERMINED]") AND ((T11_CrossFunctionalTeam.CFT) Is Not Null) AND ((T11_CrossFunctionalTeam.CFT_Description)="Commander's Assessment Board") AND ((T01_Billets.TFFMS_Last_Update) Is Not Null))
    ORDER BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder;
    There's time for a "teaching moment" and there's time for implementation (and streamlining later on). Right now, I'm falling into the latter category. I'd like to solve the issue at hand. WHERE and HAVING can be dealt with later on. Right now, I'm trying to stay focused on updating the strCriteria or strSQL to avoid the syntax error. If you have any specific change recommendations, I truly welcome your feedback.

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John - I went back to review your post #6.

    It would be truly awesome if you could include what the full line should look like... thank you so much for your help in advance.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you meant me, just get rid of the field name and =:

    "And (" & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _

    though it's hard to be sure with all the extraneous parentheses that the query editor adds.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    There's time for a "teaching moment" and there's time for implementation (and streamlining later on). Right now, I'm falling into the latter category. I'd like to solve the issue at hand. WHERE and HAVING can be dealt with later on. Right now, I'm trying to stay focused on updating the strCriteria or strSQL to avoid the syntax error. If you have any specific change recommendations, I truly welcome your feedback.

    this is my last attempt to try to get you to understand

    if you use select distinct, you don't use group by or having. And you seem to be ignoring the other part of my post

    for example

    (T01_Billets.TFFMS_Last_Update) Is Not Null


    T01_Billets.TFFMS_Last_Update is not selected or grouped on - so you cannot use it with HAVING you have to use it with WHERE
    So this is a teaching moment. If you aren't prepared to be taught you'll never get to implementation.

    So if you insist on using HAVING, remove the criteria that cannot be used in HAVING or include them in your group by part of the sql - but then your result will be wrong

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    John -- finally... all I needed was that line of code.

    Code:
    And (" & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _
    Sometimes, things that are easy can be complicated (or can be made complicated). Based on what I experienced this weekend, I knew that line contained the error.

    At this time, the SQL is outputting one PDF after the other in very rapid fashion. However, after report #26 is created, the VBA "crashes" and highlights line "qdf.SQL = strSQL".

    Again, it's a bit stumbling since the first 26 reports were generated in just a few seconds. Is there a limit on how many listbox items I can run through a loop at once?


    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub cmdExportReport_Click()
    
        'Declare variables
        Dim db As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim varItem As Variant
        Dim strCriteria As String
        Dim strSQL As String
    
        Dim ReportPath As String
        Dim ReportPathMsgBox As String
        Dim ReportFileName As String
        Dim OutputPathFileName As String
    
        Dim NumReports As Integer
    
        'Get the database and stored query
        Set db = CurrentDb()
        Set qdf = db.QueryDefs("Q353_MET_CFT_Individual")
    
        'Reports are saved to the below file path -- upon change, ensure to update both **actual report path** AND **message box report path**
        ReportPath = "C:\Users\dellc\Documents\01 -- Employers (Present)\USFF\Databases\Projects\System Management Database\Architecture v02\CFT Reports\METs\MET CFT Report - "
        ReportPathMsgBox = "C:\Users\dellc\Reports\MET CFT"
       
        'ReportPath = "\\naeanrfkfs101v\USFF02$\NFLT_FLTFORCOM-HQ_N00060\N02 Directorate\N02D\Projects\N1S1 -- Database Modification\Reports -- CFT and OPT\MET CFT Reports\MET CFT Report - "
        'ReportPathMsgBox = "...\N02D\N1S Project\...\MET CFT Reports"
        
        'Loop through the selected items in the list box and build a text string
        If Me!lstMET_CFTs.ItemsSelected.Count > 0 Then
            For Each varItem In Me!lstMET_CFTs.ItemsSelected
                strCriteria = "T11_CrossFunctionalTeam.CFT_Description = '" & Me!lstMET_CFTs.ItemData(varItem) & "'"
    
                'Build the new SQL statement incorporating the string
                 strSQL = "SELECT T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder, " & _
                         "T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T11_CrossFunctionalTeam.CFT_Owner " & _
                         "FROM T11_CrossFunctionalTeam RIGHT JOIN ((T01_Billets LEFT JOIN T96_METS ON T01_Billets.aa_CSS = T96_METS.Core_CSS) LEFT JOIN T00_JunctionTable_BCFT " & _
                         "ON T01_Billets.BilletIDpk = T00_JunctionTable_BCFT.BilletIDfk) ON T11_CrossFunctionalTeam.CFTIDpk = T00_JunctionTable_BCFT.CFTIDfk " & _
                         "GROUP BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder, " & _
                         "T11_CrossFunctionalTeam.CFT_Category, T11_CrossFunctionalTeam.CFT, T11_CrossFunctionalTeam.CFT_Description, T11_CrossFunctionalTeam.CFT_Owner, " & _
                         "T01_Billets.TFFMS_Last_Update " & _
                         "HAVING (((T96_METS.MET_Type) <> '[MET_Type -- TO BE DETERMINED]') And ((T11_CrossFunctionalTeam.CFT) Is Not Null) And (" & strCriteria & ") And ((T01_Billets.TFFMS_Last_Update) Is Not Null)) " & _
                         "ORDER BY T96_METS.MET_Type, T96_METS.MET, T96_METS.UJTL, T96_METS.StandardizationSupportingTask, T11_CrossFunctionalTeam.CFT_CategorySortOrder;"
                                        
                 'Apply the new SQL statement to the query
                 qdf.SQL = strSQL
                             
    
                 'Replaces any potential forward slashes in the N-Code (e.g., N2/N39) since "/" cannot be used as part of the filename
                 ReportFileName = Replace(Me!lstMET_CFTs.ItemData(varItem), "/", "_") & ".pdf"
                 OutputPathFileName = ReportPath & ReportFileName
    
    
                 'Outputs **all** file(s) to specified file path
                 'DoCmd.OutputTo acOutputReport, "R55_MET_CFT_Individual", acFormatPDF, OutputPathFileName, False
                 
                 'Outputs only file(s) **with records** to specified file path
                 If DCount("*", "Q353_MET_CFT_Individual") > 0 Then
                    DoCmd.OutputTo acOutputReport, "R55_MET_CFT_Individual", acFormatPDF, OutputPathFileName, False
                    NumReports = NumReports + 1
                 End If
                 
            Next varItem
    
            'Throw message box upon successful extraction of the PDFs
            MsgBox NumReports & " MET CFT Participation reports were exported to the following location: " & ReportPathMsgBox, vbInformation, "Information"
    
        Else
        
            'Throw message box in the event user did not select any N-Codes prior to report execution
            MsgBox "Please select one or more CFTs!", vbInformation, "Information"
            
        End If
    
        'Empty the memory
        Set qdf = Nothing
        Set db = Nothing
    
    End Sub
    Attached Thumbnails Attached Thumbnails Error 3075.JPG  

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 2
    Last Post: 03-11-2015, 03:40 PM
  3. "AND" or "&" in the FROM clause of a query
    By kelann in forum Queries
    Replies: 21
    Last Post: 11-09-2012, 08:41 AM
  4. Custum Functions in Query "WHERE" Clause
    By trb5016 in forum Queries
    Replies: 1
    Last Post: 02-15-2012, 03:30 PM
  5. Report "WHERE" clause not working
    By rsearing in forum Reports
    Replies: 4
    Last Post: 05-26-2009, 02:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums