report question

A

annduguay

Hi,
I've got a table that has a date field. I want to create a report that
will extract all the information for selected date range. Can I do
this? If so, can anyone help?
Thx.
Ann
 
T

tina

you can write a query based on the table, and set criteria on the date
field, as

Between #SomeDate# And #SomeOtherDate#

then base your report on the query, rather than directly on the table. there
are several ways to put specific dates in the criteria. do you want to
"hard-code" the dates into the query design? or do you want the user to
enter the dates in controls on a form (usually the easiest way to control
the data entry)? tell us what you have in mind so we can help you set it up
correctly.

hth
 
A

annduguay

Thanks hth! I want the user to enter the date range. I now have that
working. I've created a report based on the query and used "WHERE
Between [Date from] And [Date to]" It's working well except for one
thing. When using the form, I get prompted for the date from and date
to but then I get another prompt "ENTER PARAMETER VALUE" for my date???
Any idea why and how to get rid of this?
Thanks again,
Ann
 
T

tina

open the query in design view. on the menu bar, click on Query | Parameters.
in the Parameter column of the dialog, enter the Date from prompt *exactly*
as it shows in the query criteria, then in the Data Type column select
Date/Time from the droplist. move to the next row in the dialog and in the
Parameter column enter the Date to prompt, then put Date/Time in the Data
Type column. click OK and save the query.

btw, "hth" is short for "hope this helps" :)

hth
 
A

annduguay

Spoke to soon.. I have another question. How would I add the selected
date range to the report header?
My apologies if these are basic Access questions. I'm just starting
out with it.
Thx again,
Ann
 
T

tina

well, i wouldn't use parameters in my query, if i needed the date values
available to the report format. if you're not familiar with VBA code,
probably the easiest way to do it is:

open the report from a form. add two unbound textbox controls to the form,
i'll call them txtDateFrom and txtDateTo. you can add a command button to
the form, to run the "open report" code, as

If Not IsNull(Me!txtDateFrom) And _
Not IsNull(Me!txtDateTo) Then
DoCmd.OpenReport "ReportName"
Else
MsgBox "Enter both a From date and a To date."
End If

in the query, replace the criteria parameters with the following criteria,
as

Between Forms!FormName!txtDateFrom And Forms!FormName!txtDateTo

in the Parameters dialog, change the parameter values to
Forms!FormName!txtDateFrom and Forms!FormName!txtDateTo.

in the report design view, add an unbound textbox control to the report
Header section, and set the ControlSource property to

=Format(Forms!FormName!txtDateFrom, "m\/d\/yyyy") & " - " &
Format(Forms!FormName!txtDateTo, "m\/d\/yyyy")

if you enter, for instance, 01/01/06 and 01/25/06 in the form controls, the
unbound textbox in the report header will display as

1/1/2006 - 1/25/2006

change the format of the dates, and the "connector", to whatever suits you.

there are other ways to set this up, without using textbox controls on a
form. but instead you have to write code to handle whatever the user enters
in "pop up" input boxes for the dates - such as invalid dates, values that
aren't dates at all, blank values, etc. it's a lot easier to use a form with
textbox controls, where you can use input masks and validation rules to make
sure the user enters appropriate values, without doing any coding at all to
handle that issue.

hth
 
A

annduguay

Hi Tina!
Thanks so much for all of your help...
Can you see where the syntax error is here?

Private Sub cmdRunRpt_Click()
If Not IsNull(myform!txtDateFrom) And Not IsNull(myform!txtDateTo) Then
docmd.OpenReport (FSA Count Report)
Else
MsgBox "Enter both a FROM date and TO date"
End If
End Sub
 
T

tina

try

Private Sub cmdRunRpt_Click()
If Not IsNull(Me!txtDateFrom) And Not IsNull(Me!txtDateTo) Then
docmd.OpenReport "FSA Count Report"
Else
MsgBox "Enter both a FROM date and TO date"
End If
End Sub

hth
 
Top