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


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
 
Ad

Advertisements

A

Allen Browne

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 <
([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.
 
Ad

Advertisements

S

Scitea

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top