Solved Issues with Filters in reports, they dissapear!!!! (1 Viewer)

[Sodslaw]

I have made a video of my issue. I dont know why but after making changes in design view (Navigation plane>right click the report>Select Design View) the Filter is blank please see video...

Before Editing...

After Editing...

Video...

Is it an Access Bug? Can it be fixed? is there a better way to make filters work?

 

[CJ_London]

your video doesn't show anything about the filter but your filter does not make sense anyway. What are you trying to achieve

 

[Sodslaw]

your video doesn't show anything about the filter but your filter does not make sense anyway. What are you trying to achieve

Hi @CJ_London Watch the video 1-2 seconds in, it shows the property sheet with the filter populated, after changing the SQL query its gone. im not sure why you beleive the video doesnt show anything. sorry. but never the less after editing the report the code in the filter dissapears.

BTW the filter works well when access doesnt delete it. Please go to the last few threads for...

Solved - CanShrink in reports detail.

Something like this should work (Dcount on that query would tell you if there are records returned or not): IIF(dCount("*","QSalesQuotationItemAcc","OrderID = " & OrderID) =0,>0,< DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)) Cheers,

www.access-programmers.co.uk

 

[Uncle Gizmo]

You may find this video of interest:-

Filter a Report from a Form

 

[CJ_London]

sorry, don't have time to go find the answer to my question for myself - but looks like UG has a solution for you

 

[arnelgp]

maybe you Remove the filter (in form view) and Save the form (Ctrl-S).

 

[Sodslaw]

ive made a nother video showing the same issue it may be a clearer. no removal or deletion of this code by me.

@Uncle Gizmo, will check out your video

 

[arnelgp]

i tested and it did delete the filter.
what you can do is Embed the filter to your select statement:

Record Source: select the field WHERE the filter here;

 

[Sodslaw]

@arnelgp
So in the where statement of the SQL, is....

WHERE (((TblPARTS.PART_NO)<>"-" And (TblPARTS.PART_NO) Not Like "AC*"));

So having this in the SQL and this code in the filter

IIF(DCount("*","QSalesQuotationItemAcc","OrderID = " & OrderID)=0,POSITION>0,POSITION < DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID))

Im guessing having both could be causing my bug, issue right? And I just need to combine the 2 statements in the SQL. can you give me some guidanace on this please?

WHERE (((TblPARTS.PART_NO)<>"-" And (TblPARTS.PART_NO) Not Like "AC*")) AND (IIF(DCount("*","QSalesQuotationItemAcc","OrderID = " & OrderID)=0,POSITION>0,POSITION < DMin("POSITION","QSalesQuotationItemAcc","OrderID = " &OrderID)));

Ill back everything up and try it.

 

[Sodslaw]

so it appears that the filter in the propery plane, is overwritten by the SQL ( i've never come accorss it before) and after moving within the where statement of the SQL, It seems to have been solved.
This seems to work...

WHERE (((TblPARTS.PART_NO)<>"-" And (TblPARTS.PART_NO) Not Like "AC*") AND ((IIf(DCount("*","QSalesQuotationItemAcc","OrderID = " & [OrderID])=0,[POSITION]>0,[POSITION]<DMin("POSITION","QSalesQuotationItemAcc","OrderID = " & [OrderID])))<>False));

Thanks for your help guys!