Using 3 criteria to filter a report?????

  • Thread starter Thread starter Scitea
  • Start date Start date
S

Scitea

Hi guys,

I am trying to sort out a form (linked to a query) for a database which will
allow the user to enter 3 criteria to filter records and view a report. The
criteria are;

1. Journal Name
2. Start Date (beginning of records the user is interested in, eg 01/01/08)
3. End Date (end of the records user is interested in, eg. 12/31/08)

I have set up the filters in the query as below (in the criteria section)
both in the same query;

1. [Forms]![Subscription Reports]![JournalName]
2. >=[Forms]![Subscription Reports]![StartDate] And <=[Forms]![Subscription
Reports]![EndDate]

There is also a field which calculates the difference days between the due
date and date received (

I have created a form with the relevant text boxes to filter, and when I try
to run the query to produce a report it always comes up blank (when there are
at least 63000 records to choose from!)

I can't figure out why this doesn't want to work.

Any help would be greatly appreciated,

Sci x
 
Suggestions:

1. In query design, declare the date parameters so Access interprets the
dates correctly. Parameters is on the Query menu (in query design.) It opens
a dialog where you will enter 2 rows:
[Forms]![Subscription Reports]![StartDate] Date/Time
[Forms]![Subscription Reports]![EndDate] Date/Time

2. If the text boxes on the form are unbound, set the Format property to
Short Date or similar, so Access understands them as dates.

3. Make sure the cursor has left the EndDate box when you run the query.
Otherwise its Value is not updated yet, and the query will not give the
desired results.

4. In case your date/time field contains a time as well as a date, change
the Criteria in the query to:
=[Forms]![Subscription Reports]![StartDate] And <
Click to expand...
([Forms]![Subscription Reports]![EndDate] + 1)

If it still fails, while the report is open, ask Access what the filter is.
Open the Immediate Window (Ctrl+G), and enter:
? Reports![YourReportNameHere].Filter

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Scitea said:
Hi guys,

I am trying to sort out a form (linked to a query) for a database which
will
allow the user to enter 3 criteria to filter records and view a report.
The
criteria are;

1. Journal Name
2. Start Date (beginning of records the user is interested in, eg
01/01/08)
3. End Date (end of the records user is interested in, eg. 12/31/08)

I have set up the filters in the query as below (in the criteria section)
both in the same query;

1. [Forms]![Subscription Reports]![JournalName]
2. >=[Forms]![Subscription Reports]![StartDate] And
<=[Forms]![Subscription
Reports]![EndDate]

There is also a field which calculates the difference days between the due
date and date received (

I have created a form with the relevant text boxes to filter, and when I
try
to run the query to produce a report it always comes up blank (when there
are
at least 63000 records to choose from!)

I can't figure out why this doesn't want to work.
Click to expand...
 
Don't you just love it when you figure it out yourself?

I had entered the Journal Name filter into the wrong criteria box, so now
that I have moved it, it works brilliantly!

Thanks anyway,

Sci x
 
Back
Top