Capture dates for a Report

  • Thread starter Thread starter Mick Horan
  • Start date Start date
M

Mick Horan

I have a table with several fields one of which is a date field.



I've created a simple Query using the various fields including the date
field.



I then added the following "Between [Enter begin date MM/DD/YYYY] And [Enter
end date MM/DD/YYYY]" in the criteria area under the date in my query.
This allows me to select what dates I want to use when I run this query.



All of this works.



I then created a very basic report to display all the fields in my query. I
run my report and asks for the begin date and then the end date. And my
report comes out just fine.



What I would like to do is somehow capture that begin and end date info the
I enter and place it in the report heading area but I can't figure out how
to do that.



Can someone tell me what steps I need to take to do this. I could send you
the three files (table, query and report) that I'm using.



I am using Access 97.



Thanks, Mick
 
Best way is to use a form onto which your user enters the two date values.
Then have your query read that form's controls for the values. You then can
do one of two things to get the date values into your report:
(1) have the control sources of two textboxes on the report be
references to the controls on the form ( =Forms!FormName!ControlName )
(2) include as calculated fields in the query two fields that get their
values from those controls ( StartDate: Forms!FormName!ControlName ) and
then have controls on the report be bound to these fields.

To do both, you must keep the form open while your report is being
"printed". Best way to do that is to run code that opens the form in dialog
mode, have a button on this form that makes it invisible after the dates are
entered, and then code in the OnClose event of the report (or the calling
form -- the form that opens the dialog form) to close the invisible form.
 
Place an input box in a function to get the start date that stores the date
in a variable. Do the same for the end date. Reference the querry to the
functions. Use the variables to put the data in the header.
 
I have a table with several fields one of which is a date field.

I've created a simple Query using the various fields including the date
field.

I then added the following "Between [Enter begin date MM/DD/YYYY] And [Enter
end date MM/DD/YYYY]" in the criteria area under the date in my query.
This allows me to select what dates I want to use when I run this query.

All of this works.

I then created a very basic report to display all the fields in my query. I
run my report and asks for the begin date and then the end date. And my
report comes out just fine.

What I would like to do is somehow capture that begin and end date info the
I enter and place it in the report heading area but I can't figure out how
to do that.

Can someone tell me what steps I need to take to do this. I could send you
the three files (table, query and report) that I'm using.

I am using Access 97.

Thanks, Mick
Click to expand...

Simplest way to print the parameters in the header of the report is to
add an unbound control to the Report Header.
Set it's control source to:
= "For sales between " & [Enter begin date MM/DD/YYYY] & " And " &
[Enter end date MM/DD/YYYY] .

The text within the brackets must be identical to the bracketed text
in the query.
 
Thanks for the help.

I started fresh. I made a new query and added a sort on the date field. I
created a form with a command button to call the report. I made a new
report using the Report Wizard. I didn't do anything special here just used
all the fields and nothing else.

I tested each process as I went. At this point I had not added any
selection criteria. Each process worked fine. Records were selected
depending on the dates I entered.

I then added the "unbound control" on the report and set it's control source
as suggested. But when I ran the report and entered the dates it printed
them as I expected, but now my report "SORT" is messed up. It looks like
it's sorting on the date field alright but now it's sorting on the field
right next to it as well.

Before control added
3/05/4 ldrp
3/30/4 2w
3/30/4 1sw
3/30/4 2sw
3/30/4 4w

After control added
3/30/4 1sw
3/31/4 1sw
4/06/4 1sw
4/07/4 1sw
4/08/4 1sw

I can't figure out why this would effect the sort???


fredg said:
I have a table with several fields one of which is a date field.

I've created a simple Query using the various fields including the date
field.

I then added the following "Between [Enter begin date MM/DD/YYYY] And [Enter
end date MM/DD/YYYY]" in the criteria area under the date in my query.
This allows me to select what dates I want to use when I run this query.

All of this works.

I then created a very basic report to display all the fields in my query. I
run my report and asks for the begin date and then the end date. And my
report comes out just fine.

What I would like to do is somehow capture that begin and end date info the
I enter and place it in the report heading area but I can't figure out how
to do that.

Can someone tell me what steps I need to take to do this. I could send you
the three files (table, query and report) that I'm using.

I am using Access 97.

Thanks, Mick
Click to expand...

Simplest way to print the parameters in the header of the report is to
add an unbound control to the Report Header.
Set it's control source to:
= "For sales between " & [Enter begin date MM/DD/YYYY] & " And " &
[Enter end date MM/DD/YYYY] .

The text within the brackets must be identical to the bracketed text
in the query.
Click to expand...
 
Thanks for the help.

I started fresh. I made a new query and added a sort on the date field. I
created a form with a command button to call the report. I made a new
report using the Report Wizard. I didn't do anything special here just used
all the fields and nothing else.

I tested each process as I went. At this point I had not added any
selection criteria. Each process worked fine. Records were selected
depending on the dates I entered.

I then added the "unbound control" on the report and set it's control source
as suggested. But when I ran the report and entered the dates it printed
them as I expected, but now my report "SORT" is messed up. It looks like
it's sorting on the date field alright but now it's sorting on the field
right next to it as well.

Before control added
3/05/4 ldrp
3/30/4 2w
3/30/4 1sw
3/30/4 2sw
3/30/4 4w

After control added
3/30/4 1sw
3/31/4 1sw
4/06/4 1sw
4/07/4 1sw
4/08/4 1sw

I can't figure out why this would effect the sort???

fredg said:
I have a table with several fields one of which is a date field.

I've created a simple Query using the various fields including the date
field.

I then added the following "Between [Enter begin date MM/DD/YYYY] And [Enter
end date MM/DD/YYYY]" in the criteria area under the date in my query.
This allows me to select what dates I want to use when I run this query.

All of this works.

I then created a very basic report to display all the fields in my query. I
run my report and asks for the begin date and then the end date. And my
report comes out just fine.

What I would like to do is somehow capture that begin and end date info the
I enter and place it in the report heading area but I can't figure out how
to do that.

Can someone tell me what steps I need to take to do this. I could send you
the three files (table, query and report) that I'm using.

I am using Access 97.

Thanks, Mick
Click to expand...

Simplest way to print the parameters in the header of the report is to
add an unbound control to the Report Header.
Set it's control source to:
= "For sales between " & [Enter begin date MM/DD/YYYY] & " And " &
[Enter end date MM/DD/YYYY] .

The text within the brackets must be identical to the bracketed text
in the query.
Click to expand...
Click to expand...

Any sorting you have done in the query is irrelevant to the sorting
done in a report.
If you wish to sort the report use the Report's Sorting and Grouping
dialog.

In Design View, click on
View + Sorting and Grouping.
Enter the field(s) you wish to sort by.
 
Fred,
You seem to know your stuff and I wondered if you would be interested in
helping me with things like this from time to time. I'm retired and do a
little volunteer Access programming (???) for the Hospital. Most of what I
do is pretty simple stuff and with the help of my books I can get through
most of it, but sometimes they want me to do things I don't know how to do
and so I come here to ask for help. This works pretty well but some times I
need a quick answer and more importantly one that makes it easy to under
stand. You sure did that on this problem. So I'm wondering if you would
take a question from time to time via your email and if you think that the
problem is something you could do (I don't mean write a complete program) I
mean just like the question I asked here. I was thinking I could send you
$10.00 a question. I have all these books but I think they assume you know
Access already. If your interested send me an email at
(e-mail address removed)

Thanks Mick



fredg said:
Thanks for the help.

I started fresh. I made a new query and added a sort on the date field. I
created a form with a command button to call the report. I made a new
report using the Report Wizard. I didn't do anything special here just used
all the fields and nothing else.

I tested each process as I went. At this point I had not added any
selection criteria. Each process worked fine. Records were selected
depending on the dates I entered.

I then added the "unbound control" on the report and set it's control source
as suggested. But when I ran the report and entered the dates it printed
them as I expected, but now my report "SORT" is messed up. It looks like
it's sorting on the date field alright but now it's sorting on the field
right next to it as well.

Before control added
3/05/4 ldrp
3/30/4 2w
3/30/4 1sw
3/30/4 2sw
3/30/4 4w

After control added
3/30/4 1sw
3/31/4 1sw
4/06/4 1sw
4/07/4 1sw
4/08/4 1sw

I can't figure out why this would effect the sort???

fredg said:
On Fri, 16 Apr 2004 16:49:04 -0700, Mick Horan wrote:

I have a table with several fields one of which is a date field.

I've created a simple Query using the various fields including the date
field.

I then added the following "Between [Enter begin date MM/DD/YYYY] And [Enter
end date MM/DD/YYYY]" in the criteria area under the date in my query.
This allows me to select what dates I want to use when I run this query.

All of this works.

I then created a very basic report to display all the fields in my query. I
run my report and asks for the begin date and then the end date. And my
report comes out just fine.

What I would like to do is somehow capture that begin and end date
Click to expand...
info
the
I enter and place it in the report heading area but I can't figure out how
to do that.

Can someone tell me what steps I need to take to do this. I could
Click to expand...
send
you
the three files (table, query and report) that I'm using.

I am using Access 97.

Thanks, Mick

Simplest way to print the parameters in the header of the report is to
add an unbound control to the Report Header.
Set it's control source to:
= "For sales between " & [Enter begin date MM/DD/YYYY] & " And " &
[Enter end date MM/DD/YYYY] .

The text within the brackets must be identical to the bracketed text
in the query.
Click to expand...
Click to expand...

Any sorting you have done in the query is irrelevant to the sorting
done in a report.
If you wish to sort the report use the Report's Sorting and Grouping
dialog.

In Design View, click on
View + Sorting and Grouping.
Enter the field(s) you wish to sort by.
Click to expand...
 
Fred,
You seem to know your stuff and I wondered if you would be interested in
helping me with things like this from time to time. I'm retired and do a
little volunteer Access programming (???) for the Hospital. Most of what I
do is pretty simple stuff and with the help of my books I can get through
most of it, but sometimes they want me to do things I don't know how to do
and so I come here to ask for help. This works pretty well but some times I
need a quick answer and more importantly one that makes it easy to under
stand. You sure did that on this problem. So I'm wondering if you would
take a question from time to time via your email and if you think that the
problem is something you could do (I don't mean write a complete program) I
mean just like the question I asked here. I was thinking I could send you
$10.00 a question. I have all these books but I think they assume you know
Access already. If your interested send me an email at
(e-mail address removed)

Thanks Mick
Click to expand...
*** snipped ***

Mick,
Thank you for this kind message.
Like yourself, I too am retired and I have no interest in 'going back
to work'.... been there! done that!
I answer question's in several of these Access news groups for free,
as do all MVP's and other frequent responders.
I've learned a lot and (I hope) helped a lot.
Ask your questions here.
The price is right. :-)>

Good luck.
 
Back
Top