where does this filter come from when i select my query?

[chaft]

This is confusing and I see no reason for this. But when I go to create a report of a query that has 31 records the first record is the only one that appears in the report.

When I run the query it works as expected, but when I go to create the report it doesn't present all the records except the very first one.

Now the second thing i don't understand is that a filter always appears every time I select query.

((Contact_Database.ContactDetailID=5))

contact_database is a table and contactdetail is a field in that table.

However I never set up such a filter! How did it create this? I've created the query twice now and still it always applies a filter. Why!?

Any ideas..I've checked the Sql and can't see anything

Here is the SQl for the query

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT superior1.ContactDetailID, Trim([SuperiorTitle] & "") AS [Position], superior1.SuperiorAddressTerm, superior1.SuperiorFullenvelopetitle, Trim([SuperiorOrderName] & "") AS OrderName, Trim([SuperiorNameofRelHouse] & "") AS [Rel House], superior1.SuperiorAidMemoir, superior1.HomeName, superior1.HomeNumber, superior1.HomeStreet, superior1.HomeCity, superior1.RegDisabled, superior1.[HomeCounty/state], superior1.[HomePostcode/ZipCode], superior1.Country, superior1.Email, superior1.MobilePhone, superior1.HomePhone, superior1.OfficePhone, superior1.Fax1
FROM superior1
WHERE (((Trim([SuperiorTitle] & "")) Like [forms]![superior]![searchsuperiortitle] & "*") AND ((Trim([SuperiorOrderName] & "")) Like [forms]![superior]![copied] & "*") AND ((Trim([SuperiorNameofRelHouse] & "")) Like [forms]![Superior]![copied2] & "*"));

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


I just don't get it, why hide my data and create random filters?

 

[dhookom]

I have never experienced or heard of issues like this. A report has a filter property. The filter property doesn't get set on its own. It can be set in the report design view or it gets set when you use code like:

DoCmd.OpenReport "rptMyRpt", acPreview, , "ContactDetailID=5"

If you create a new report based on your query, does it behave this way? Are you displaying your field values in the detail section?

What do you see if you add a text box to the Report Header Section with a control source of:
=Count(*)

Duane MS Access MVP

 

[chaft]

ok think i've spotted my mistake.. i put all my textboxes in the page header and NOT the detail section. That explains why I could not get more than 1 record.

However I am still unclear why this filter appears in the filter line of the form.

in answer to your reply...


firstly there is no filtered code which loads the report like you specified


stDocName = "Superior"
DoCmd.OpenReport stDocName, acPreview

secondly when i add the text box it says "31"



I don't know why this filter appears though...fortunately it doesn't seem to affect the results..but if anyone knows...

 

[dhookom]

However I am still unclear why this filter appears in the filter line of the form.

Are you suggesting there is a value in the Filter property of the report? Are you the only user of the MDB?

Duane MS Access MVP

 

[chaft]

I am the sole author and user of the database. I include a link to the picture of the filter..I don't know how i created it. I must had but I want to get rid of it. it keeps appearing each time i create a report of that query.

http://download.yousendit.com/09EBD4DF5AB1CD3B

 

[dhookom]

I would open the query in datasheet view, right click, and select remove filter/sort. Then save the query. Open the report in design view and remove the filter property value and save the report.

Duane MS Access MVP

 

[JoeAtWork]

If you open the report while specifiying a WHERE criteria, then go into design mode, yo will see the WHERE clause in the Filter property.

However, next time you open the report it will change the Filter property.

 

[chaft]

dhookom i tried that and that didn't stop the filter appearing.


JoeAtWork i think you are trying to say that if i'm running a report from the query that the report will naturally have a filter. However the query doesn't have a where statement that matches the filter that appears in the filter box.

Fortunately it doesn't affect the results. So I'll think I'll call it day on this quirk. I don't understand it but I guess I don't need to if it doesn't affect my results.

thanks for you contributions though all of you

 

[dhookom]

The Filter On property is set to No so the filter will have no effect. I have never seen a report where you change the property and save the report and the property value doesn't stick.

Duane MS Access MVP