Results 1 to 8 of 8
  1. #1
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72

    Form to add query data

    Morning, I am extremely new to all of this and am trying to create a form for the user to specify query data. So, I have dates, names, location, outcome. I have put the criteria for each element in the "Criteria" box so it reads [Forms]![frm_Report Builder]![PupilName] or whichever is the relevant box on the form. Of course, the query is now returning nothing because it's looking for blank records if the user has left the form blank when I actually want all the records. What i want to happen is if they specify nothing, then they get all the records, if they specify a year group, they get all that year group, if they specify a year group and outcome they get all records with both of those. Does that make sense?! Should I be trying to use an AND or and OR or am I way off?!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If you only have 1 control, a combo box for the user to pick, then there should be no problem. Query looks at the 1 item.
    but If you have more than 1 control on the form the user can pick 'filter criteria', then you need to use vb.

    using the OPEN button (or filter)
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event
    if null, ignore.
    if not, apply.


    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

  3. #3
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    Hi, thank you for the reply. I have copied this code into the button click event and edited to coincide with my labels etc. But where is this query data going to go, e.g. where do it now appear in another sheet that I could report from?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    my example used a continuous for the show all records, then FILTER the records.
    If you want a query to use it, you must rewrite the query sql and then it can be used for reports too.
    'alter the query with the criteria....


    Code:
    '----------------
    sub btnOpenQry_click()
    '----------------
    dim sWhere as string 
    dim qdf as querydef
    
    sWhere = " where 1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    set qdf = currentdb.querydefs("qsMyQry")
    qdf.sql = "Select * from table " & swhere
    qdf.close
    docmd.openquery qdf.name
    
    
    end sub

  5. #5
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    Oh wow! This has got complicated quickly, I thought it would be "relatively" straight forward

  6. #6
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    I'll try it but don't really understand the code. Do you have any links to online lessons that might help explain this?

  7. #7
    dwheatley is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    22
    In design view of query there is a box below 'criteria' titled 'or'.

    This is where you can tell the query what to do if the relevant box in the form is left blank.

    Beneath where you have '[Forms]![frm_Report Builder]![PupilName]' as your criteria, you should put '[Forms]![frm_Report Builder]![PupilName] Is Null' (minus quotation marks). This should mean that if a user fills in all the other information (dates, location, etc.), but ONLY leaves the PupilName field blank, then your query will return all of the pupils in a certain location etc.

    To replicate this across the rest of the query, you need to apply the same principle, but it gets a little trickier with each new combination of search. For example, say that you want to only search by 'Date', meaning that you leave all other search criteria blank. You would need to have a row in your query design in which there is '[Forms]![frm_Report Builder]![Date]' in your Date column, and then '
    [Forms]![frm_Report Builder]![PupilName] Is Null' in Pupil column adjacent, and the same 'Is Null' approach to the other adjacent fields for location etc.

    What this means is that the query now knows what search to perform when one field is blank (Is Null). To ensure that your search returns results no matter which field is left blank in the form, you need to repeat this process for each possible event in your query design. E.g. perhaps both name and date are left blank but all other fields are filled in. Then you would do Is Null for name and date fields but not for the rest.

    Hope this helps!

  8. #8
    School Boy Error is offline Advanced Beginner
    Windows 7 32bit Access 2013 64bit
    Join Date
    Oct 2012
    Posts
    72
    Thank you, that makes sense. I've now done it writing the query in SQL one point at a time!
    Code:
    SELECT Behaviour.[Incident Date], Behaviour.[Pupil Name], Behaviour.[Year group], Behaviour.Role, Behaviour.[Abuse Type], Behaviour.Issue, Behaviour.Consequence, Behaviour.[Staff Involved], Behaviour.OutcomeFROM BehaviourWHERE (Behaviour.[Incident Date] Between Forms![frm_Analysis Builder]!StartDate and Forms![frm_Analysis Builder]!EndDate+1)And (Behaviour.[Year group]=Forms![frm_Analysis Builder]!YearGroup Or IsNull(Forms![frm_Analysis Builder]!YearGroup)) and (Behaviour.[Role]=Forms![frm_Analysis Builder]!Role Or IsNull(Forms![frm_Analysis Builder]!Role)) and (Behaviour.[Abuse Type]=Forms![frm_Analysis Builder]!Type Or IsNull(Forms![frm_Analysis Builder]!Type)) and (Behaviour.[Outcome]=Forms![frm_Analysis Builder]!Outcome Or IsNull(Forms![frm_Analysis Builder]!Outcome));

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

Similar Threads

  1. Replies: 5
    Last Post: 04-09-2017, 06:12 PM
  2. Replies: 1
    Last Post: 06-03-2016, 08:23 PM
  3. How to get the data from a query into a form
    By ManuelLavesa in forum Forms
    Replies: 6
    Last Post: 05-03-2016, 07:01 PM
  4. Replies: 7
    Last Post: 03-29-2016, 09:11 AM
  5. Replies: 10
    Last Post: 02-12-2014, 08:26 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