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?
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?
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
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
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.