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?
PdetersAsked:
Who is Participating?
 
frankyteeCommented:
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
0
 
peter57rCommented:
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.
0
 
PdetersAuthor Commented:
How would I store them somewhere?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
frankyteeCommented:
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
0
 
PdetersAuthor Commented:
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
0
 
frankyteeCommented:
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
0
 
PdetersAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.