Results 1 to 7 of 7
  1. #1
    Troxs is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    7

    Search Query with Form Values keeps crashing

    I have a form, based on a table with 18 fields, and I'm trying to build a form with a list box that allows the users to enter in data ranges to search for. Basically they enter a Start Date and End date, then if they choose to search results for any other field they would enter the desired range and requery a listbox to display the results, from there they can print or export the report as a .pdf



    Currently I have query with all records with the only criteria as:

    Between [myTextbox] And [MyTextBox2].

    From there I built the list box on that query but added:

    Between [myTextbox] And [MyTextBox2] Or [myTextbox] Is Null And [myTextBox2] Is Null

    Being that I have to add that criteria to 18 fields it seems to be too much for access to handle... Is there an easier way to complete this? I'm very novice when it comes to VBA, but I'm willing to learn, although it may need to be explained to me as though I'm 5. I do have a mild background in C++ from college -- on a basic level -- so I can follow some of the logic, but I'm at a lost with the syntax.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    joins are a bit like maths formula, sometimes you need to use brackets

    so how is your criteria to be interpreted?

    (Between [myTextbox] And [MyTextBox2] Or [myTextbox] Is Null) And [myTextBox2] Is Null


    Between [myTextbox] And [MyTextBox2] Or ([myTextbox] Is Null And [myTextBox2] Is Null)

  3. #3
    Troxs is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    7
    I can get the code to work as intended, but when I try to use that code for so many cretiria on one query it goes to "program not responding"

    I was thinking that maybe there was a way to write either an SQL loop, or VBA to make this happen properly.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can use vba - I presumed that was what you were using anyway. Not sure what you mean by a sql loop.

    rather than trying to write your code in one line, break it down into smaller components and build up a string which you can the assign to the filter, querydef whatever. That way you can test for null and if it is, don't include it in the string

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not making any sense of the criteria string - perhaps because what it applies to is missing. If this were a date comparison like
    WHERE [tblOrders].[TheDate]Between [myTextbox] And [MyTextBox2], the first part makes sense. The second part does not
    Or [myTextbox] Is Null And [myTextBox2] Is Null

    If a textbox has no value in it, the function will return True when it evaluates. So the latter part of what looks like a where clause becomes
    Or True And True which makes no sense to me.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @micron,
    I think the OP is in design view of a query - in the Criteria row of the grid for a date field the OP entered
    Between [myTextbox] And [MyTextBox2] Or [myTextbox] Is Null And [myTextBox2] Is Null

    so a field name is not necessary.
    Granted, the syntax might not be correct, but the OP says
    Being that I have to add that criteria to 18 fields it
    there would be 17 more Criteria rows in the query but the the specific criteria posted wouldn't work for the fields that are not date fields.
    I would need to see what values the other 17 controls have.

    If there was a form with around 19 unbound controls, I would probably use VBA to create a WHEREcondition string to use in the DoCmd.OpenForm command.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @ssanfu - granted, but I still maintain the is null will evaluate to true or false and doubt that can also satisfy something that can also be between. My remark had more to do with not being positive because no field was identified.
    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. Query Crashing Access
    By JonReedTDC in forum Queries
    Replies: 4
    Last Post: 04-21-2016, 04:33 PM
  2. Replies: 24
    Last Post: 04-08-2015, 02:59 PM
  3. Replies: 2
    Last Post: 12-08-2014, 08:55 AM
  4. Search Query crashing database
    By Hamm in forum Forms
    Replies: 5
    Last Post: 01-10-2013, 12:29 PM
  5. search main form using the values in the subform
    By haritbhasin in forum Forms
    Replies: 1
    Last Post: 10-23-2012, 11:48 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