Link to home
Start Free TrialLog in
Avatar of Pdeters
Pdeters

asked on

Access 2003 Report - want to show criteria dates

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?
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Pdeters
Pdeters

ASKER

How would I store them somewhere?
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
Avatar of Pdeters

ASKER

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
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
Avatar of Pdeters

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of frankytee
frankytee
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial