Results 1 to 13 of 13
  1. #1
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10

    Query Help

    Hello all,



    right to it; I am trying to query a table from 3 possible fields from a form that a user can enter the criteria; So lets say I have Field1 Field2 and Field3 from Table1; Field3 will always have a value entered by the user.

    Obviously if user enters information for all three fields my query works nice, using the `like`command, and setting the form fields value default as `*`, which works for the most part; but has some glitches, and in some cases won`t return any results. I need help in the event that the user only wants to search Field1 and Field3 or Field2 and Field3 ultimately leaving Field2 and Field1, respectiviely, in a null state;

    so essentially it would be like saying if Field1 has a value then do nothing, but if Field1 is null then use a * wild card value to search all.

    any help would be appreciated

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use a continuous form to show all records,
    then when the user fills in the boxes, then clicks the FIND button, use vb to build the where clause:

    Code:
    sub btnFind_click()
    dim sWhere as string
    
    if not IsNull(txtBox1) then sWhere = sWhere & " and [field1]='" & me.txtBox1 & "'"
    if not IsNull(txtBox2) then sWhere = sWhere & " and [field2]='" & me.txtBox2 & "'"
    if not IsNull(txtBox3) then sWhere = sWhere & " and [field3]='" & me.txtBox3 & "'"
    
    if sWhere = "" then
       me.filterOn = false
    else
           'remove the 1st 'and'
        sWhere = mid(sWhere,5)
       me.filter = sWhere
       me.filterOn = true
    endif
    end sub

  3. #3
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10
    so I added that to my button event does not seem to work for me, I'm obviously doing something incorrect;

    Here is the form button
    '------------------------------------------------------------
    ' LSD_Query_Click
    '
    '------------------------------------------------------------
    Private Sub LSD_Query_Click()
    On Error GoTo LSD_Query_Click_Err


    DoCmd.OpenQuery "LSD_Query", acViewNormal, acEdit
    DoCmd.Requery ""




    LSD_Query_Click_Exit:
    Exit Sub


    LSD_Query_Click_Err:
    MsgBox Error$
    Resume LSD_Query_Click_Exit


    Dim sWhere As String


    If Not IsNull(Query_Section) Then sWhere = sWhere & " and [Section]='" & Me.Query_Section & "'"
    If Not IsNull(Query_Twp) Then sWhere = sWhere & " and [Township]='" & Me.Query_Twp & "'"
    If Not IsNull(Query_Rge) Then sWhere = sWhere & " and [Range]='" & Me.Query_Rge & "'"


    If sWhere = "" Then
    Me.FilterOn = False
    Else
    'remove the 1st 'and'
    sWhere = Mid(sWhere, 5)
    Me.Filter = sWhere
    Me.FilterOn = True
    End If
    End Sub

    here is my query sql

    SELECT Work_Order.Work_Order, Work_Order.Company_Name, Work_Order.Survey_Type, Work_Order.Lot, Work_Order.Block, Work_Order.Plan, Work_Order.Section, Work_Order.Township, Work_Order.Range, Work_Order.Fieldbook
    FROM Work_Order
    WHERE (((Work_Order.Section) Like [Forms]![Search by LSD]![Query_Section]) AND ((Work_Order.Township) Like [Forms]![Search by LSD]![Query_Twp]) AND ((Work_Order.Range) Like [Forms]![Search by LSD]![Query_Rge]));

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    1 - please use code tags and minimize the space between lines. It makes it much easier to read your code
    2 - if you start with field 3 since that will always have a value as you say, you don't need to remove a leading 'and'
    3 - what are the names after Me.? If these are query fields, you cannot reference them that way.
    4 - I think the suggestion assumed all values would be text since that is the way it was presented. If you have numerical values, it will not work as posted
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10
    1. Gotcha!
    2. ok - so reverse order the query? the 'range' will be "field 3"
    3. they are the form text boxs used to relate to the query
    4. should of clarified I will primarily be dealing with just numerical value input into the form

    appreciate the patience.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    before any of us elaborate on anything, this is a concern
    I will primarily be dealing with just numerical value
    The single quotes are required to handle text. #'s are for dates, nothing for numbers. You need to identify which are which. If a textbox could receive more than one data type, that's problematic. Note that numbers can be entered as text - it depends on how you have designed the table fields. Numerically, an ascending order would be like 1,2,3...10,11 as you'd expect. Alphabetically (when numbers are stored as text) 1, 10, 11, 12...19, 20. 523 comes before 6. Get the idea?

  7. #7
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10
    All three fields data types are set to 'text' in the table

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't understand WHY you are opening a query in code, then setting the FORM field?????
    OOPS, the filter code will NEVER execute!
    Only the lines in BLUE will execute.
    Code:
    Option Compare Database
    Option Explicit
    
    '------------------------------------------------------------
    ' LSD_Query_Click
    '
    '------------------------------------------------------------
    Private Sub LSD_Query_Click()
        On Error GoTo LSD_Query_Click_Err
    
        DoCmd.OpenQuery "LSD_Query", acViewNormal, acEdit
        DoCmd.Requery ""          'don't know why this command is here???
    
    LSD_Query_Click_Exit:
        Exit Sub
    
    
    LSD_Query_Click_Err:
        MsgBox Error$
        Resume LSD_Query_Click_Exit
    
    '------------------------------------------------------------
    '  because of the "Exit Sub" line,
    ' the following code will NEVER execute
    '------------------------------------------------------------
        Dim sWhere As String
    
        If Not IsNull(Query_Section) Then sWhere = sWhere & " and [Section]='" & Me.Query_Section & "'"
        If Not IsNull(Query_Twp) Then sWhere = sWhere & " and [Township]='" & Me.Query_Twp & "'"
        If Not IsNull(Query_Rge) Then sWhere = sWhere & " and [Range]='" & Me.Query_Rge & "'"
    
        If sWhere = "" Then
            Me.FilterOn = False
        Else
            'remove the 1st 'and'
            sWhere = Mid(sWhere, 5)
            Me.Filter = sWhere
            Me.FilterOn = True
        End If
    End Sub


    To filter the FORM recordset, I would expect to see something more like:
    Code:
    '------------------------------------------------------------
    ' LSD_Query_Click
    '
    '------------------------------------------------------------
    Private Sub LSD_Query_Click()
        On Error GoTo LSD_Query_Click_Err
    
        Dim sWhere As String
    
        If Not IsNull(Query_Section) Then sWhere = sWhere & " and [Section]='" & Me.Query_Section & "'"
        If Not IsNull(Query_Twp) Then sWhere = sWhere & " and [Township]='" & Me.Query_Twp & "'"
        If Not IsNull(Query_Rge) Then sWhere = sWhere & " and [Range]='" & Me.Query_Rge & "'"
    
        If sWhere = "" Then
            Me.Filter = ""
            Me.FilterOn = False
        Else
            'remove the 1st 'and'
            sWhere = Mid(sWhere, 5)
            Me.Filter = sWhere
            Me.FilterOn = True
        End If
    
    LSD_Query_Click_Exit:
        Exit Sub
    
    LSD_Query_Click_Err:
        MsgBox Err.Description & " - Error number: " & Err.Number
        Resume LSD_Query_Click_Exit
    End Sub

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was getting to all that, but wanted to know if numbers or dates were involved first. Since the answer is no, it can be this short because it was declared that the 3rd field will never be absent - thus the test for sWhere being "" is not needed.
    Code:
    Private Sub LSD_Query_Click()
    Dim sWhere As String  'althoug I would use strFilter because this is a filter, not a Where clause
    
    On Error GoTo LSD_Query_Click_Err
    sWhere = "[Range]='" & Me.Query_Rge & "'"
    If Not IsNull(Query_Section) Then sWhere = sWhere & " And [Section]='" & Me.Query_Section & "'"
    If Not IsNull(Query_Twp) Then sWhere = sWhere & " And [Township]='" & Me.Query_Twp & "'"
    
    Me.Filter = sWhere
    Me.FilterOn = True
    
    LSD_Query_Click_Err:
    MsgBox "Error " &  Err.Number & ": " & Err.Description
    
    End Sub
    What the answers lack is a similar means of removing the filter once applied. Aside from naming the variable in a manner more conducive to what it really does as noted in the code, I'd say for trouble shooting purposes, what the control is that takes this event is obscure. If it's a command button, that control should have a name more indicative of that - cmdApplyFilter or some such thing. Check out naming: http://www.access-programmers.co.uk/...d.php?t=225837
    A couple of other changes I made is to not bother re-directing the error handling since there's nothing to clean up, plus I like to structure my error messages a bit differently.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10
    I am understanding why I'm having troubles with the method presented; My form is only a simple form, not bound to any records. My approach was so the user can enter 3 sets of criteria for my query to identify, apply, and return a dataset view of results that match. I am understanding the filter method you're presenting, I'll make a new form and try.

    Why would a filter be more beneficial than a query in this sense?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Or edit your current form. No one is saying don't have a query because from a practical point of view, it's necessary. More on that in a minute.

    Make a query that works first. Then build a from from it, possibly using the wizard. If you do that, rename all the controls using what you learned from the naming convention link I gave you. Access will name the controls the same as the query or table fields and this can cause problems. Save changes often.
    Two form possibilities are Split form vs a form with a subform datasheet. I confess I haven't used split forms so I can't comment on that. I get more control the second way but it's harder for a novice. I'll have to post about what I know best, but it might be easier for you to look for tutorials on how to create a form/subform. Video is better than all the words I could write but here's a condensed version.

    Sounds like your main form needs controls that will take the input to filter the results. Often, combo boxes are used for this as it restricts users to picking from a list and avoids typos. You'd also create a form whose record source is your working query and set datasheet as the default view. Add a subform control to the main form and set the source of the subform control to be the datasheet form. When that's working, you'll have a form with controls and a datasheet of records based on the query. Post back if you need help to continue from that point (you might do it all yourself after researching as suggested).

    As for filtering. IF you have many thousands of records that will load, it can result in better performance if you pre-filter with a query first. Regardless, once the records are loaded into the form, it is less efficient to keep modifying the record source for the form, thereby continually requerying the source data. This takes resources, which are sometimes stretched in a network environment. Better to load all the needed records in one shot, then FILTER the results. Applying a new filter time and time again does not requery from the source, it only alters the display of the records. The original data is there - you just see different parts of it. If you know Excel, it's really the same thing. You know all the rows are in the spreadsheet; you're just not seeing them all when a filter is applied.

  12. #12
    newbie_204 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2015
    Posts
    10
    Thank you for the help so far! here is what I`ve come up with

    Code:
    '------------------------------------------------------------
    ' Plan_Query_Click
    '
    '------------------------------------------------------------
    Private Sub Plan_Query_Click()
    On Error GoTo Plan_Query_Click_Err
    
    
        DoCmd.OpenQuery "Plan_Query", acViewNormal, acReadOnly
        DoCmd.ApplyFilter "filter.block", "(([Forms]![Search by Lot/Block/Plan]![Query_Block] Is Null) Or ([Work_Order]![Block] Like [Forms]![Search by Lot/Block/Plan]![Query_Block] & ""*""))", ""
        DoCmd.ApplyFilter "filter.lot", "(([Forms]![Search by Lot/Block/Plan]![Query_Lot] Is Null) Or ([Work_Order]![Lot] Like [Forms]![Search by Lot/Block/Plan]![Query_Lot] & ""*""))", ""
        DoCmd.RunCommand acCmdRefresh
    
    
    
    
    Plan_Query_Click_Exit:
        Exit Sub
    
    
    Plan_Query_Click_Err:
        MsgBox Error$
        Resume Plan_Query_Click_Exit
    
    
    End Sub
    Individually the filters work how I want them to; but I`d like to have them run in a sequence, this seems to run both filters but only displays the values of the latter filter. ie. filter all records that match the `block`from the query and then filter by `lot` out of all those records that match the `block` filter in the query.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Individually the filters work how I want them
    That's interesting, because in more than one reply here it was suggested that you base a form on your query and filter the form. I've never heard of or tried to filter an open query and would have said it wouldn't work, but I see it does. However, it has to be the active window. So if you intend to continue on with filtering an open query rather than a form it's based on, know that you cannot filter a filter (it seems that's what you're trying to do).

    If you don't intend to view the whole set of records, why are you not just restricting the records that the query returns? Or have the query do what the first filter does, then apply the second one? And what is "filter.block"?? Looks like an actual filter (which is usually a named query in itself), which is supposed to be doing the filtering. So you seem to be doing both. AFAIK, if you include both, the clause overrides the named filter. VERY confusing. Be that as it may, here's what your code could also look like (I didn't bother modifying the error routine again as it doesn't seem to be your preference).

    Code:
    Private Sub Plan_Query_Click()
    Dim strFilter as String 'used only if the filter is a sql type clause
    
    On Error GoTo Plan_Query_Click_Err
    'a filter example based on a clause like a WHERE clause
    strFilter = "(([Forms]![Search by Lot/Block/Plan]![Query_Block] Is Null) Or ([Work_Order]![Block] Like [Forms]![Search by Lot/Block/Plan]![Query_Block] & ""*""))", ""
    
    DoCmd.OpenQuery "Plan_Query", acViewNormal, acReadOnly
    DoCmd.ApplyFilter , strFilter
    'OR a filter example based on a named filter
    'DoCmd.ApplyFilter NameOfFilter
    
    Exit Sub
    
    Plan_Query_Click_Exit:
        Exit Sub
    
    Plan_Query_Click_Err:
        MsgBox Error$
        Resume Plan_Query_Click_Exit
    
    End Sub
    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.

Tags for this Thread

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