Rick wrote.
Hi,
We have created a report which displays all records in the
database table. I would like to be able to filter this so
that it shows only records where my "DateEntered" field is
on or after a certain date.
Can someone explain a way to do this? Also, it would be
nice for me to easily be able to change the date criterion
such as via a parameter pop-up window.
Thanks very much in advance for any help.
Rick Johnston
-----------------------------------------------------------
Rick,
Here is the way I got it to work. Maybe a very ugly way
to do it but it works for me.
Step one, I created a form and add the ACTIVEX CONTROL.
(under the insert menu) "Calendar Control 10.0" I give
the name to the calendar in properties STARTDATE and I
have another ENDDATE. They are on the form
FM_REPORTS_CALENDAR.
Then I have a query as the input to the report I created
(a little different for reports that are charts.). In
this example I and looking for records that match a LookUp
table's data so I can sort on it.
************************************************
Query <qry_RPT_input>
SELECT DISTINCT <LU_tbl.DESC>, Count(*) AS Num
FROM (<tbl_main> INNER JOIN <tbl_sub> AS <tbl_sub1>
ON <tbl_main.mainID> = <tbl_sub1.mainID>)
INNER JOIN <LU_tbl> ON <tbl_sub1.LU_tblID> = <LU_tbl>
WHERE (((tbl_sub1.xDate) Between [FORMS]!
[Fm_Reports_Calendar]![StartDate] And [FORMS]!
[Fm_Reports_Calendar]![EndDate]))
GROUP BY <LU_tbl.Desc>;
************************************************
Then on your form that has the calendar(s) on it, create
the CMB to fire up the report. Use the wizard to do the
OPENREPORT for you and all the report. The report just
needs to use the <qry_RPT_input> as it's source
TIP: When you add the ACTIVEX Calendar, it will always
come up with the date that you created it. To fix that,
put something like the following in on the form's ON LOAD
event. Here I have the Start Date calendar default to 180
days ago, and the EndDate calendar set to today's date.
************************************************
StartDate.Value = DateAdd("d", -180, Date)
EndDate.Object.Today
EndDate.Value = EndDate.Object.Value
*************************************************
hth
Ivan
