Results 1 to 4 of 4
  1. #1
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18

    Create a Report from a Filter Button

    Hello everyone, I have a Form where I have inserted buttons that, if clicked, filter the data of the form. I know it's not the best method to apply filters to a Form, but I'm just trying to understand how some dynamics work, with some exercises that aren't very sensible. So I created various data including the "price" group with values ​​from 0 to 2000, and I created a normal button with a macro with a filter function, with a Where condition:

    [price] Between "0" And "1000".

    Then I created a macro button that opens the report, and I created a query to match the report that contained all the data including "price". Now I had already done a similar exercise with a combo box and in the query that I had matched to the report I had put, in the criteria:

    Like Nz ([Form]! [PrizeForm]! [Combobox1]; "*")


    and it had worked very well, but now if I use the "like Nz" with the normal filter button that I explained above, it doesn't work. So how do I link the report with the query to the filter button results in my form?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your main issue is possibly that the price field is numeric data type and you are providing values that are strings.

    One way would be to open the report based on the query returning a larger set of records, then filter the report by creating and applying the filter clause. The filter clause is like a sql WHERE clause without the word 'WHERE'. For this approach
    https://docs.microsoft.com/en-us/off...md.applyfilter

    There are other ways, such as to use a dynamic sql statement or a query that uses form field references as parameters. Any of these require vba code (except the parameterized query). You'll find that most of us don't use macros, embedded or otherwise but might fiddle with a macro in some cases.

    Could also research "ms access open filtered report" or similar. There will be millions of hits, no doubt.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Micron View Post
    Il tuo problema principale è che il campo del prezzo è di tipo numerico e stai fornendo valori che sono stringhe.

    Un modo sarebbe quello di aprire il report in base alla query che restituisce un insieme più ampio di record, quindi filtrare il report creando e applicando la clausola di filtro. La clausola di filtro è come una clausola WHERE di sql senza la parola 'WHERE'. Per questo approccio
    https://docs.microsoft.com/en-us/off...md.applyfilter

    Esistono altri modi, ad esempio utilizzare un'istruzione sql dinamica o una query che utilizza riferimenti ai campi del modulo come parametri. Ognuno di questi richiede codice vba (tranne la query con parametri). Scoprirai che la maggior parte di noi non usa macro, incorporate o meno, ma in alcuni casi potrebbe giocherellare con una macro.

    Potrebbe anche cercare "ms access open filtered report" o simili. Ci saranno milioni di visite, senza dubbio.

    thank you for your answer, I already knew some of the alternative methods you told me, and I know that my method is not very sensible when it comes to filters and reports, but I want to do it this way because I want to understand some concepts and dynamics. it is as if I were doing some "stupid" exercises but which I need to understand various things. And in fact I wanted to use macros with filters, and be able to match a query of a report to a classic and normal button that has a macro filters that acts on a Form. Unfortunately, on the Internet I can't find any solutions because everyone uses VBA or other methods. much more sensible, and so I came here because I know that you are experts, I have already learned a lot from you. So I hope that some of you can give me a hand but sticking to this method, I know it is a heavy request, but I hope for your patience.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Fair enough, but what about my first sentence? I think you need to confirm the data type.
    Since this is just an exercise, you should be able to copy the db, compact/repair it, zip it then upload here? "Doesn't work" doesn't reveal much.

    If you're saying that this syntax
    Nz ([Form]! [PrizeForm]! [Combobox1]; "*")

    worked elsewhere I'm surprised. Maybe Nz(Forms!PrizeForm.Combobox1;"*") ?
    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. Replies: 3
    Last Post: 11-04-2019, 07:17 PM
  2. Command button to filter report and print
    By tsamrein in forum Forms
    Replies: 2
    Last Post: 09-29-2017, 10:37 AM
  3. Applying a filter to a report via a button
    By MowsMek in forum Reports
    Replies: 6
    Last Post: 03-16-2015, 01:04 AM
  4. Replies: 3
    Last Post: 11-27-2012, 07:20 AM
  5. Create "reset filter" button
    By vanlanjl in forum Access
    Replies: 0
    Last Post: 03-03-2009, 07:36 AM

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