Results 1 to 9 of 9
  1. #1
    Pjarman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    6

    Reports to create queries


    I would like to create a user friendly form in access 2016 where a user who knows nothing about access can select the criteria and from this by clicking on a button a report is created.

    My table has 17 headings which are short text, two dates and one hyperlinks and I would like the option to create a report from a form by selecting a max of 4 criteria

    e.g. I would like a drop down boxes where I could select criteria and a button that makes the report.


    Your advice and guidance in this request is greatly appreciated.

    Peter

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event

    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 & "'"
    
    docmd.OpenReport "rMyReport",acViewPreview ,,sWhere
    
    end sub

  3. #3
    Pjarman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    6

    Thank you for your advice and guidance it was very useful

    Quote Originally Posted by ranman256 View Post
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event

    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 & "'"
    
    docmd.OpenReport "rMyReport",acViewPreview ,,sWhere
    
    end sub

    I am now attempting phase 2 of my plan:

    As I am simple I have been using query criteria to try and achieve the result that I am after:

    Overview I have 300+ records I created a query linked to my Form with the following headings

    [txtRank/Title] [txtFirst Name] [txtLast Name] [txtAppointment] [txtDepartment] [txtOrganisation] [txtCity]

    and for each Criteria I used the following code pasted into the first Criteria row (changing for each field) :

    Like "*" & [Forms]![Contacts Search Form]![txtRank/Title] & "*" Or [Forms]![Contacts Search Form]![txtRank/Title] Is Null

    I did this because I wanted the user who might not know all the field details but could type a few letters in the relevant field and produce a result, and I wanted access to look in the field and say; I am looking for data to be entered here but if it is blank (null) no problem I will show all the results for this field and carry on through the query until if finds a criteria and produce a result or if there is no data entered produces a result showing all data.

    It worked...

    until I ran it a few times and a message "query too complicated" I checked the view Design and It's taken my code and changed it to Like "*" & [Forms]![Contacts Search Form]![txtRank/Title] & "*" which was copied and pasted in the same column 20+ times and it created its own columns with the Field heading [Forms]![Contacts Search Form]![txtRank/Title] and pasted Is Null 20+ times in that column.

    I think I am trying to put a square peg in a round hole.

    However your continued support and advice in how to resolve this issue would be greatly appreciated.

  4. #4
    Pjarman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    6
    So I have managed to achieve part of my aim I created a form and in design view I inserted a button, cancelled the wizard, right clicked and selected build. I amended SQL and it worked see below code:
    Private Sub Command20_Click()




    '----------------
    Dim sWhere As String


    sWhere = "1=1"
    If Not IsNull(First_Name) Then sWhere = sWhere & " And [First_Name]=' & First_Name & '"
    If Not IsNull(Last_Name) Then sWhere = sWhere & " and [Last_Name]='" & Last_Name & "'"
    If Not IsNull(Appointment_Title) Then sWhere = sWhere & " and [Appointment_Title]='" & Appointment_Title & "'"




    DoCmd.OpenReport "Address Test Report", acViewPreview, , sWhere


    End Sub

    Example when I typed the first name Peter it works however if I typed Pete it would give me a blank report I've tried combinations of wildcard * and % if anyone could advice I would be grateful.

    Kind Regards

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Maybe show some examples of sql statements using wildcards that didn't work (whatever that means) because LIKE with wildcard instead of = is what I'd expect you'd need to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Pjarman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    6
    Thank you please see below, Peter OK Pete nah

    Dim sWhere As String


    sWhere = "1=1"
    If Not IsNull(First_Name) Then sWhere = sWhere & " and [First_Name]Like'" & First_Name & "'"
    If Not IsNull(Last_Name) Then sWhere = sWhere & " and [Last_Name]Like'" & Last_Name & "'"
    If Not IsNull(Appointment_Title) Then sWhere = sWhere & " and [Appointment_Title]Like'" & Appointment_Title & "'"




    DoCmd.OpenReport "Address Test Report", acViewPreview, , sWhere


    End Sub

    Peter nah compile error

    Dim sWhere As String


    sWhere = "1=1"
    If Not IsNull(First_Name) Then sWhere = sWhere & " and [First_Name]Like'" "*" & First_Name & "*" "'"
    If Not IsNull(Last_Name) Then sWhere = sWhere & " and [Last_Name]Like'" & Last_Name & "'"
    If Not IsNull(Appointment_Title) Then sWhere = sWhere & " and [Appointment_Title]Like'" & Appointment_Title & "'"




    DoCmd.OpenReport "Address Test Report", acViewPreview, , sWhere


    End Sub

    I know I'm doing something wrong any direction you could give would be greatly appreciated.

    Regards

    Peter

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    try something like
    ...And [Last Name] LIKE """ & "*" & variableHere & "*"""
    Watch out for spacing in your sql strings. Sometimes you are forgiven and sometimes not ---> [First_Name]Like
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Debug.Print sWhere to see what is produced and where any errors are.

  9. #9
    Pjarman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    6
    Success, Job done, Thank you for your advice and guidance.

    I now have a one page form that users can enter details and depending on the report they need all they have to do is press the relevant button and its done, they don't need to know the exact spelling and can leave fields blank if not known.

    I must ensure the name is correct for each button in this case Command20 and the report title is changed to the one I need DoCmd.OpenReport "Address Test Report", acViewPreview, , sWhere. I copy and paste the text and it is all good. Thanks again.

    Private Sub Command20_Click()




    '----------------
    Dim sWhere As String


    sWhere = "1=1"
    If Not IsNull(Rank_Title) Then sWhere = sWhere & " and [Rank_Title] LIKE """ & "*" & Rank_Title & "*"""
    If Not IsNull(First_Name) Then sWhere = sWhere & " and [First_Name] LIKE """ & "*" & First_Name & "*"""
    If Not IsNull(Last_Name) Then sWhere = sWhere & " and [Last_Name] LIKE """ & "*" & Last_Name & "*"""
    If Not IsNull(Appointment_Title) Then sWhere = sWhere & " and [Appointment_Title] LIKE """ & "*" & Appointment_Title & "*"""
    If Not IsNull(Department) Then sWhere = sWhere & " and [Department] LIKE """ & "*" & Department & "*"""
    If Not IsNull(Organisation) Then sWhere = sWhere & " and [Organisation] LIKE """ & "*" & Organisation & "*"""


    DoCmd.OpenReport "Address Test Report", acViewPreview, , sWhere


    End Sub

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

Similar Threads

  1. Queries to reports
    By Cowboys822 in forum Queries
    Replies: 7
    Last Post: 12-22-2017, 02:56 PM
  2. reports vs queries?
    By RLehrbass in forum Access
    Replies: 4
    Last Post: 08-03-2015, 07:22 PM
  3. Replies: 7
    Last Post: 01-16-2014, 09:17 AM
  4. Replies: 1
    Last Post: 06-20-2011, 03:56 PM
  5. Parameter Queries & Reports
    By Nixx1401 in forum Reports
    Replies: 6
    Last Post: 05-07-2011, 08:09 PM

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