Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    9

    Unanswered: Query By Form Ignoring Criteria

    Hi all,
    I'm pretty new to Access, so any help I can get is appreciated. I'm using a form I've created to search a query. When I assign criteria to the query, it finds the records filtered by the first three criteria and simply ignores the fourth. This is just a test so far, but if I can't get 4 to work, 60-some isn't going to work either. My criteria format is [Forms]![Dredge_Data_Search]![txtCritDate] Or [Forms]![Dredge_Data_Search]![txtCritDate] Is Null. This works for my Date box, my File box and my Time box. When I use that as [Forms]![Dredge_Data_Search]![txtAmmonia1] Or [Forms]![Dredge_Data_Search]![txtAmmonia1] Is Null the search returns all records regardless of the number typed in my Ammonia1 search box.

    Any help?
    Attached is the sql for my query.
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Best to post that stuff on the page inside code tags.

    Unfortunately, Access makes a right mess of SQL so even then this particular query would be hard to read. I think this is one where it would be easier to see a screen shot of the query builder, ensuring we can see all the WHERE conditions please.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2009
    Posts
    9

    Screenshots

    Thanks for the tip

    Here are the screenshots of the criteria builder. Note that the additional lines in the "Or" section were put in by Access not by me. All my criteria was originally written on one line. Hopefully someone can find an answer for me.

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You want loads of parameters, and you want to work out a SQL statement that will filter based on a match of all the parameters the user has selected a value for, and ignore the parameters that they have not selected a valaue for - correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2009
    Posts
    9

    Sounds right

    That sounds about right. I posted the sql because it was the only way I could think of illustrating what was going on. I'll ultimately have 64 different criteria that can be searched, while ignoring any blank boxes. Every criteria will be numbers.

    Thanks for the help

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. You can do this sort of thing with a handful of criteria but 64! You are getting into thousands and thousands of combinations.

    You need to use dynamic SQL by VBA for this. Something like (pseudo code):
    Code:
    sql = "SELECT col1, col2 FROM myTable WHERE 1 = 1"
    
    If Me.thisTextbox > "" THEN
    sql = sql & " AND col1 = '" & Me.thisTextbox & "'"
    End if
    
    
    If Me.anotherTextbox > "" THEN
    sql = sql & " AND col2 = '" & Me.anotherTextbox & "'"
    End if
    
    '... and so on
    DEBUG.Print sql
    Once the correct SQL starts printing out, your VBA is good and you need to execute it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2009
    Posts
    9

    Thanks

    Thanks for the help. I figured I'd need to get into VBA, but I needed a starting point. I'll give this a shot.

    Thanks

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That code will work for textual data, it's simpler for numbers:

    Code:
    If Me.thisTextbox > "" THEN
       sql = sql & " AND col1 = " & Me.thisTextbox
    End if
    and so on.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •