none
Can you create a search in an Access database report? RRS feed

  • Question

  • I know that you can create an unbound form to create a search in an Access Database, but an employee wants to know if we can create a search on a Report.  The way they have the report setup is that you have to enter a start and end date first.  
    Wednesday, November 11, 2015 1:42 PM

Answers

  • You can filter what data is displayed in the report.  The best way to do this is at the time you open the report using DoCmd.OpenReport "your report name",acViewPreview,,"where condition to show the desired record(s)".
    • Marked as answer by JeffyK Wednesday, November 11, 2015 1:55 PM
    Wednesday, November 11, 2015 1:49 PM

All replies

  • You can filter what data is displayed in the report.  The best way to do this is at the time you open the report using DoCmd.OpenReport "your report name",acViewPreview,,"where condition to show the desired record(s)".
    • Marked as answer by JeffyK Wednesday, November 11, 2015 1:55 PM
    Wednesday, November 11, 2015 1:49 PM
  • Thanks for the quick response!  I was thinking about this and was wondering if it really made sense for them to have a search by user in the report, especially since they have to enter a start and end date.
    Wednesday, November 11, 2015 1:50 PM
  • I will sometimes search a lengthy report by saving it as a PDF then I’ll open the PDF and use ctrl-F to find what I’m searching for.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Wednesday, November 11, 2015 1:53 PM
  • That's a great work around too.  Thanks again!
    Wednesday, November 11, 2015 1:55 PM
  • I don't know how your report is set up currently.  I suspect that you're using a parameter query for the report which asks the user to enter the date values.  Is that correct? 

    Regardless, I would design an input dialog that asks for the desired criteria, making start and end date required inputs, and anything else optional.  Using a button to open the report, put your criteria together and create your where condition, then execute the DoCmd.OpenQuery() with the results.

    Wednesday, November 11, 2015 1:57 PM
  • I admit I am not completely proficient with Access.  I was given this report and asked if we can do the following and I am also not good with coding at all either.  

    When I go into the VB code for the report the only thing that shows up is the fact that it's protected by a password.  Not sure if that really helps or not.

    Wednesday, November 11, 2015 2:08 PM
  • With what I suggested, there's no need to alter the report. 

    How is the report currently opened by the user?

    Wednesday, November 11, 2015 2:12 PM
  • Ok that's what I thought.

    They just double click it in Access and enter their dates, then it displays the information for that date range along with employee name and some other info.

    Wednesday, November 11, 2015 2:16 PM
  • So it's as I thought, the report uses a parameter query as its record source.  Because the database is protected, you will first need to get administrative rights to modify the design.

    Next you will want to find the query that supplies the data for your report.  You can open the report in design view, then open the properties dialog.  Under the Data tab you will see at the very top, the property: Record Source.  In the field next to it will either be an SQL string, or the name of a saved query.  To edit the query, place your cursor in the field and press Ctrl+F2, or you can click the button to the right [...].

    You will want to switch to SQL view and remove the WHERE clause.  Before doing this, make sure that the clause only has DateFrom and DateTo as its conditions.  If not, then just remove those conditions.  After doing that, close the query editor and save the changes.  You can also close your report and save the changes.

    Once you've done all that, post back and I will guide you further.

    Wednesday, November 11, 2015 2:31 PM
  • I followed the steps, but I don't see a WHERE clause in here, just AS.
    Wednesday, November 11, 2015 2:54 PM
  • Can you copy the SQL and post it here?  Thanks...
    Wednesday, November 11, 2015 3:49 PM
  • Hi Jeffy,

    Is "Coding Summary-Part 2" another query?

    On a separate note, can you post your database on OneDrive so that I can look at it directly?

    • Edited by RunningManHD Wednesday, November 11, 2015 5:48 PM
    Wednesday, November 11, 2015 5:46 PM
  • Yes, there is another query for it.

    Unfortunately due to HIPAA regulations, I won't be able to post this on OneDrive.


    • Edited by JeffyK Wednesday, November 11, 2015 6:01 PM
    Wednesday, November 11, 2015 6:01 PM
  • Jeffy,

    Please post the SQL to the "Coding Summary..." query.

    Thanks,

    RM

    Thursday, November 12, 2015 8:22 PM
  • Take a look at the example here.  You can download the actual database right at the bottom of the page.

    http://www.fontstuff.com/access/acctut19.htm

    Does that help?


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Wednesday, November 18, 2015 1:13 PM
  • Sorry I never responded, a lot of things happened and I had to put this on the backburner.  Now I've also been asked to fix other issues with this as well and I admit I'm in over my head on it.  

    I'll try out the link that you pasted above.

    Thanks!

    Friday, January 15, 2016 3:18 PM
  • It will definitely work.  You just need to spend a little time on it, get acquainted with it, and you'll figure it out.

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, January 15, 2016 3:55 PM