Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    mmerig is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2023
    Location
    Idaho, Teton Basin
    Posts
    7

    Overly complex select query difficult to edit

    The problem is a select query that provides input to a form that displays lots of information in 22 text boxes and an image box. The form has a subform, and the database is one-to-many.



    The select query is written to with a search form with several combo box controls. The controls allow a multi-level search,and each control can be used alone or in combination, or any or all of them can be left blank. (If all are blank, then every record shows up in the search). There are no calculations or updates to the data, just simple selections.

    The form and query worked fine, and the query was constructed via Access (via a wizard I think, it has been months so not sure) while making the search form. I see the structure in design view, but the SQL version is very long (1583 words). If I add another search control, I have to edit the criteria and there is a pattern to it that I expand on. But the number of rows for criteria expand geometrically (2, 4, 8, etc) by the number of columns, and that is getting unwieldy. There are many "Is Null" criteria along with select statements. Should I send the SQL version for someone to look at?

    Maybe there is a much simpler way to make the query. After adding another combo box search level, it took a while to edit it and make it work. It totally works except for one search option in particular. I can probably fix that but I see lots of tedious criteria entries ahead if I want to add more search levels. There are six levels now, and that does not seem like a lot. I would like to add three or four more

    I am new to the forum, and not an expert (obviously) with Access. If there was a way that Access could modify the query code as I add controls (for searching), that would be ideal.

    Thanks.

  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
    I would build the SQL in code. Jason has a FAQ here:

    http://www.baldyweb.com/BuildSQL.htm

    Generally:

    strSQL = "SELECT ...FROM...WHERE 1=1 "

    and then you test a control. If it has a value add a criteria:

    strSQL = strSQL & "AND FieldName = " & Me.ControlName & " "

    Jason shows the delimiters that need to be used for text and date/time data types. At the end, either set the form's recordsource to the SQL or change a saved query's SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    mmerig is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2023
    Location
    Idaho, Teton Basin
    Posts
    7
    Thanks, this could be helpful. What do you mean by "build the SQL in code"? Would that be in VBA?


    The examples in Baldyweb are very simple. What I am dealing with is very repetitive SQL code (and many rows and columns in deign view of the query). Each part is simple enough, but taken all together it is unwieldy. As I mentioned, the SQL code is 1583 words, seven single-spaced pages if copied and pasted into Word. Perhaps i should start from scratch, and put in all the controls I need at once, and let Access build the code automatically like it did before. But still, the amount of code seems unreasonable for what I a trying to do.

    I am using Access 2010.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    In addition to Paul's suggestions, if not already done also helps to alias your tables to one or two slightly meaningful letters e.g alias 'tblExcelSource' as 'E' or 'XL' or 'S'. Avoid spaces and non alphanumeric characters in table and field names - saves the need for the square brackets. Personally I don't use underscores -the dot gets lost when scanning the code.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 11 Access 2019
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    DynamicSynchronizingComboBoxes-davegri-v03.zip

    This might be helpful. It will build a recordsource query for the form with any number of comboboxes you choose to add.
    Pretty easy to maintain the VBA once you get the code concept down.
    The sample DB holds 70,000 records and performance is very fast.

    Click image for larger version. 

Name:	search.png 
Views:	38 
Size:	59.1 KB 
ID:	49777
    Last edited by davegri; 02-23-2023 at 07:50 PM. Reason: more

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Rows should not be a factor?, just the fields or as you call them columns.

    You should be looking at the second page of that link? http://www.baldyweb.com/BuildSQL2.htm
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  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
    Quote Originally Posted by mmerig View Post
    Thanks, this could be helpful. What do you mean by "build the SQL in code"? Would that be in VBA?


    The examples in Baldyweb are very simple. What I am dealing with is very repetitive SQL code (and many rows and columns in deign view of the query). Each part is simple enough, but taken all together it is unwieldy. As I mentioned, the SQL code is 1583 words, seven single-spaced pages if copied and pasted into Word. Perhaps i should start from scratch, and put in all the controls I need at once, and let Access build the code automatically like it did before. But still, the amount of code seems unreasonable for what I a trying to do.

    I am using Access 2010.
    Yes, using VBA. Your query is so complicated (I'm guessing) because of all the optional criteria. How much of your SQL is before the word WHERE, vs after? In the method I'm describing, you wouldn't have that complexity associated with the optional criteria. You'd only add the fields to the criteria that the user had filled in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    mmerig is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2023
    Location
    Idaho, Teton Basin
    Posts
    7
    Thanks to all. I think i may start from scratch using advice here and elsewhere.

    Regarding pbaldy's last post, the WHERE statement is very near the beginning, about 1/10th of the way down into the code. So alot of the complexity is due to the criteria. This is obvious in the query's deign view too, but I thought it was unavoidable.

    I did look at the second page in the baldyweb link. Also thanks for the hint on using unbound combo boxes.

    The search form controls (combo boxes) set up the query, and another form displays the search results. A command button macro makes that happen. I cannot fit the search filters (combo boxes) and results on one form due to space limitations (it would be too crammed with information), and I also want the image box as big as possible. Maybe that form shift does not matter, but thought I would mention it in case it does.

  9. #9
    mmerig is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2023
    Location
    Idaho, Teton Basin
    Posts
    7
    Well, thanks to the advice here, I have started over with the query and search form, and made a lot of progress. The query is very simple now, and the combo boxes on the search form write to it properly. The file-size dropped by a megabyte after changing out the old query.

    But one problem remains:

    If a combo-box entry is blank, the query does not select any records.

    Before I tied the combo boxes with a form statement in the criteria, I used manually entered values in the query's design view (e.g., "Yes", "No". specific string values that match the record table). Blank criteria values did not matter, the record selections worked as expected.

    The criteria statement for a given field is [Forms].[Form name].[name of combo box]. The combo boxes are unbound.

    My previous query had <Is Null> criteria, for when a combo box value was missing (MS Access automatically made the query). It makes logical sense for one or more combo boxes to have no user entry, so I am stuck with that.

    How can I make the query handle null values? My old, huge query had <Is Null> criteria, lot's of them, plus multiple, repetitive criteria pointing to bound combo box values, but I don't want to go back to that. Should there be an OR parameter somewhere?

    Thanks again.

  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
    What does the query look like now? If you're building it in code, you simply wouldn't add anything to the criteria if the control was empty. In other words, if you had 20 options but the user only filled out 4 of them, the resulting query would only have 4 fields in its WHERE clause. It sounds like you still have criteria for all the fields in the query, so you'd likely use this method:

    http://www.theaccessweb.com/queries/qry0001.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Conditionally include criteria. Another reference to guide you 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.

  12. #12
    mmerig is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2023
    Location
    Idaho, Teton Basin
    Posts
    7
    Thanks pbaldy. That OR statement with the IS Null at the end works perfectly. There is also a lot of good advice in the link provided by June7 (allenbrown.com). Thanks to all again.

  13. #13
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653
    @mmerig You might want to check this out. It got me started on a fairly complex search form. I changed his naming convention to use variables instead, for pairing controls.
    Adding or changing fields is pretty easy.
    I would say that buying the template, for me, was well worth the $.

    Search Form 2.0 in Microsoft Access (599cd.com)

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by mmerig View Post
    Thanks pbaldy. That OR statement with the IS Null at the end works perfectly. There is also a lot of good advice in the link provided by June7 (allenbrown.com). Thanks to all again.
    If memory serves, you may find the design view significantly altered next time you open the query. I always build the SQL in code with more than 2-3 optional criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    653

    A sample search form built in

    Cont. of post#13

    I was running out the door this morning, so I couldn't post this image:

    Click image for larger version. 

Name:	230205Search1.jpg 
Views:	15 
Size:	234.7 KB 
ID:	49784

    It's a search with combo boxes, that allows for and/or and sorting by ascending and descending of each column.
    There is a default begin portion to the Sql, then the code just has to add a Where and Order By.
    The record selected either returns an ID to a form or a field that uses the ID as a foreign key.
    The message box shows the built SQL just before the me.recordsource gets set to it.

    It sounds like maybe you solved your problem, but if you have to rewrite the search, or make another the 599cd.com might help.

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

Similar Threads

  1. Update Query with Select Function to Complex
    By KelliganP in forum Queries
    Replies: 10
    Last Post: 01-27-2021, 02:50 PM
  2. Replies: 13
    Last Post: 05-24-2013, 05:54 AM
  3. Replies: 23
    Last Post: 07-29-2011, 04:24 PM
  4. Replies: 5
    Last Post: 07-29-2011, 11:54 AM
  5. very difficult (for me!) SELECT query
    By igorbaldacci in forum Queries
    Replies: 1
    Last Post: 12-02-2008, 03:30 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