Access 2003 Report - want to show criteria dates

Posted on 2007-10-13
Last Modified: 2013-11-28
I have an Access 2003 databas.  When I run a report I want it to show the dates that I have chosen to run from the query.

In the query I had between [start date[ and [end date]
I put [start date] and [end date] in text boxes on the report and worked just fine.

I now am running a macro that creates a new table with critera from existing tables and then when that is fnished the report opens up. Is there a way I can get the dates I put in the first query criteria to create the new table that the report is based off of to show up on the report?
Question by:Pdeters
    LVL 77

    Expert Comment

    Only if you store them somewhere.
    If you use a form to input your dates, rather than parameter prompts, then the report could get the dates from the form, assuming it was still open when the report ran.

    Author Comment

    How would I store them somewhere?
    LVL 19

    Expert Comment

    you can just use a variable to store the value of the criteria. that way you don't even need to redefine the query that drives the report, just filter the report with the criteria when you open it, eg below, replace names accordingly

     dim sWhere As String
        'set value of variable when you define your query

        sWhere =  " '" & me.txtboxDate & "' between [start date] and [end date] "  
    'or you may need # depening on your pc settings
        sWhere =  "#" & me.txtboxDate & "'# between [start date] and [end date] "  

          'open report
        DoCmd.OpenReport "myReport", acViewPreview, , sWhere

    Author Comment

    where would I put this -
    I have a macro that runs a query and then opens the report.
    I need to run the query first because it is creating a new table that the report uses
    LVL 19

    Expert Comment

    avoid using macros, the simplest way would be vba code.
    in the click event of whatever command button on your form, something like:
    docmd.runsql "whatever sql syntax to create your table"

    then execute the code i posted previously

    Author Comment

    Right now I have the report opening and in the report on the onopen event is the macro that first runs the query that creates the new table for the report. I am not using a form
    LVL 19

    Accepted Solution

    you are trying to set the recordsource of the report as it opens. i wouldn't try that if it was me and i'm not sure it would even work.
    much better to open the report via a form where the user enters whatever criteria, then you open the report filtered to that criteria as i posted previously.
    it's the most elegent method as that way you dont even need to change the report's query/table, just filter it accordingly

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Wrong number of arguments 2 17
    Microsoft Access Table name 3 20
    Access 2003, modify export spec 11 14
    font dialog in office 64bit 12 12
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now