Filter items on reports

[t5amec]

I would like to be able to filter reports.
For example, I have thousands of records in a table but I only want the ones dated from 16/08/99 to 16/08/02.
Is this possible?

 

[SQLBI]

You need to base your report on a query, not on your table.

SELECT Field1, Field2, Field3.... FROM tblTable WHERE Date BETWEEN #16/08/1999# AND #16/08/2002#;

When you've written the query, go to the properties of your report and change it's RecordSource property to that of your query.

The report will then only display the required records.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

Hi,
That was just an example, I would like to be able to enter in certain dates, i.e different dates, everytime i view the report.

 

[SQLBI]

Modify the query as follows...

SELECT Field1, Field2, Field3.... FROM tblTable WHERE Date BETWEEN [Enter Start Date] AND [Enter End Date];

This will prompt the user with an InputBox to enter the date range of the report.


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

Cheers dude,
What about say invoice numbers?
Eg, I'm looking for all deliveries from invoices 303-025 to invoices 303-028.
Or as we have hundreds of Locations can you apply a filter so it shows just one location?
Eg, I'm looking for all delivereis to RAIL BOX EAST.

 

[SQLBI]

It's just a matter of modifying your WHERE clause in the queries:

WHERE InvNo = [Enter Invoice Number]
WHERE Location = [Enter Location]

The possibilities are endless!

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

It comes up with the Enter Parameter Value box but if I enter a value, or a location nothing ends up in the table.

In the Criteria under the field i put in this:
"WHERE Location"=[Enter Location]

Why is nothing coming up?

 

[SQLBI]

Does your code have the "'s in it, if so delete them.

Also, is your location a String(Text) or a number...

If it's text, try:

WHERE Location = "'" & [Enter Location] & "'";

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

The "s come in automatically.
so the code goes from this:
WHERE Location = "'" & [Enter Location] & "'"
TO
"WHERE Location"="'" & [Enter Location] & "'"
Could this be a problem?
The fields are text fields.
Is it cAsE SeNsItIvE?

 

[SQLBI]

They shouldn't be case sensitive, but it shouldn't but the "'s in.

Is your field name Location, or is it something else?

Can you post your Full SQL statement please?

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

er... is this what you want?
SELECT [UPDATE:TESTINGMERGED].[Note No], [UPDATE:TESTINGMERGED].[Invoice No], [UPDATE:TESTINGMERGED].Date, [UPDATE:TESTINGMERGED].Location, [UPDATE:TESTINGMERGED].Equipment, [UPDATE:TESTINGMERGED].QTY, [UPDATE:TESTINGMERGED].Rate, [UPDATE:TESTINGMERGED].Total, [UPDATE:TESTINGMERGED].[Payment Cert], [UPDATE:TESTINGMERGED].Description
FROM [UPDATE:TESTINGMERGED]
WHERE (("WHERE Location"="'" & [Enter Location] & "'&quot);

yeah the field name is Location.

 

[SQLBI]

You had 2 WHERE clauses, copy this over what you've got and it should work.

SELECT * FROM UPDATE:TESTINGMERGED
WHERE Location="'" & [Enter Location] & "'";


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[t5amec]

Still not working...

My SQL View is now:
SELECT [UPDATE:TESTINGMERGED].[Note No], [UPDATE:TESTINGMERGED].[Invoice No], [UPDATE:TESTINGMERGED].Date, [UPDATE:TESTINGMERGED].Location, [UPDATE:TESTINGMERGED].Equipment, [UPDATE:TESTINGMERGED].QTY, [UPDATE:TESTINGMERGED].Rate, [UPDATE:TESTINGMERGED].Total, [UPDATE:TESTINGMERGED].[Payment Cert], [UPDATE:TESTINGMERGED].Description
FROM [UPDATE:TESTINGMERGED]
WHERE Location = "'" & [Enter Location] & "'";

should it of worked?

 

[SQLBI]

It's possible that you might not need the "'" try the code below and i'm sure we'll get there in the end.

SELECT [UPDATE:TESTINGMERGED].[Note No], [UPDATE:TESTINGMERGED].[Invoice No], [UPDATE:TESTINGMERGED].Date, [UPDATE:TESTINGMERGED].Location, [UPDATE:TESTINGMERGED].Equipment, [UPDATE:TESTINGMERGED].QTY, [UPDATE:TESTINGMERGED].Rate, [UPDATE:TESTINGMERGED].Total, [UPDATE:TESTINGMERGED].[Payment Cert], [UPDATE:TESTINGMERGED].Description
FROM [UPDATE:TESTINGMERGED]
WHERE Location = [Enter Location];


Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk