Results 1 to 3 of 3
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    SQL statements for Multiconditional Query

    OK, I have completely confused myself. I have a report generating form (see image below) that I am trying to create multiple reports from, but the syntax and order is making my head spin. Really, It is the multiselect listbox that is throwing me off.



    Click image for larger version. 

Name:	Screenshot (5).png 
Views:	12 
Size:	32.2 KB 
ID:	37950

    I am using Select Case for the Report Type and Report Criteria as below when populating the listbox.

    Code:
    Select Case Me.cbxReportType.Value
            Case "Defect Reports"
                Select Case Me.cbxRptCriteria.Value
                    Case "Category"
                        If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
                            MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
                            strSQL = "SELECT DISTINCT Category FROM DefectEvents"
                        Else
                            strSQL = "SELECT DISTINCT Category FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
                        End If
             Case "Inventory Reports"
             **etc.**
    And I know that I have to create the SQL statement for the report in a loop for each item selected in the listbox. I am just not sure how. This is what I have so far, but I know it is not right.



    Code:
    If Me.tbxDate1 = "Optional" Or Me.tbxDate2 = "Optional" Then
          MsgBox "Omitting a date range will list all available records.", vbInformation, "ATTENTION"
          With Me.lstRptSearchResult          For lngRow = 0 To .ListCount - 1
                    If .Selected(lngRow) Then
                         strSQL = "SELECT * FROM DefectEvents WHERE Me.cbxRptCriteria = ????? ;"
                    End If
              Next lngRow
          End With  
    
    Else
          With Me.lstRptSearchResult
              For lngRow = 0 To .ListCount - 1
                    If .Selected(lngRow) Then
                         strSQL = "SELECT * FROM DefectEvents WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
                    End If
              Next lngRow
          End With   
    End If
    I hope my questions makes some kind of sense. The red question marks are my biggest quest, but structure and syntax too. The second part of the If statement is also in question because I have to search by both the Listbox selection and between the date range.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just build the WHERE clause, not an entire SQL statement. Review http://allenbrowne.com/ser-50.html and http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It's a very commonly asked question and there are TONS of examples out there, so did you look at any to see how it's usually done? I ask because usually you declare a variant for a list item and use the listbox .SelectedItems property rather than loop through every item to see if it's selected. Kind of like
    For Each varItem in Me.myList.ItemsSelected
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with Query and IF Statements in VB
    By trent.bourne in forum Access
    Replies: 9
    Last Post: 06-13-2018, 06:33 PM
  2. 15 IIF Statements in a Query
    By hiker8117 in forum Queries
    Replies: 9
    Last Post: 05-31-2018, 05:11 AM
  3. Query Criteria Using IIf Statements
    By MintChipMadness in forum Queries
    Replies: 6
    Last Post: 01-26-2013, 06:55 PM
  4. Query iif statements
    By beckkks in forum Queries
    Replies: 1
    Last Post: 04-20-2012, 03:03 PM
  5. Query for IIF statements
    By SpotoR1 in forum Queries
    Replies: 2
    Last Post: 08-26-2009, 06:57 AM

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