Split form filter question

[Dunnopat]

The idea is that when the form is loaded, some rows are filtered out. The rows are ones where

EXPIREDATE is before current day and LASLEFT is 0
AND rows where
REMAINING is 0 and and LASLEFT is 0.


It's been a while since I last did much with access. Can anyone give me a hint? I can make it show only the rows where those rules apply but I want to make it so it shows everything but those.

 

[SpentGeezer]

A simple method is to have 2 querries.

Qry1:
SELECT Table1.*
FROM Table1
WHERE (((Table1.EXPIREDATE)>Now()) AND ((Table1.LASLEFT)<>0));

Qry2
SELECT Qry1.*
FROM Qry1
WHERE (((Qry1.LASLEFT)<>0) AND ((Qry1.REMAINING)<>0));

Then set your form's data source to Qry2

 

[Dunnopat]

A simple method is to have 2 querries.

Qry1:
SELECT Table1.*
FROM Table1
WHERE (((Table1.EXPIREDATE)>Now()) AND ((Table1.LASLEFT)<>0));

Qry2
SELECT Qry1.*
FROM Qry1
WHERE (((Qry1.LASLEFT)<>0) AND ((Qry1.REMAINING)<>0));

Then set your form's data source to Qry2

But the data source can't be changed. There needs to be an option to view nonfiltered data. There are also many buttons that filter the data in some way. If I "filter" the data in data source, then the data that is left out can't be accesses with the form.

 

[SpentGeezer]

Try something like this:

 

[Dunnopat]

Try something like this:

Edit: Sorry, can't open it. Not allowed.
It's hard to explain my situation too when I can't really tell anything. Even the names of the fields are changed.

I was wondering, could I do the following:

I make a query that selects those rows that I don't want to see in the form. One of the fields in the row is ID.

Then, in access filter:

WHERE formblabla.ID <> queryblabla.ID.

Would that work?

I'm sick and tired and I'll try that soon. I don't know if that is slow or not but the database is not really that big. I can't think or concentrate. It took me 3 hours to realize that in order to get remaining amount, I need to do Price - AlreadyPaid. I hate fever.

 

[boblarson]

You can leave your regular record source and then use the filter to do the work (like you were thinking). You would have a button to apply it or have the On Load event apply it and then you can have a button to remove the filter:

Me.Filter = "([EXPIREDDATE]< Date() AND [LASLEFT] = 0) OR ([REMAINING] = 0 AND [LASLEFT]=0)"
Me.FilterOn = True

To remove the filter use

Me.Filter = ""
Me.FilterOn = False

 

[Dunnopat]

You can leave your regular record source and then use the filter to do the work (like you were thinking). You would have a button to apply it or have the On Load event apply it and then you can have a button to remove the filter:

Me.Filter = "([EXPIREDDATE]< Date() AND [LASLEFT] = 0) OR ([REMAINING] = 0 AND [LASLEFT]=0)"
Me.FilterOn = True

To remove the filter use

Me.Filter = ""
Me.FilterOn = False

Doesn't that filter show only the data I don't want to show? Or does it hide the data that is either ([EXPIREDDATE]< Date() AND [LASLEFT] = 0)
or
([REMAINING] = 0 AND [LASLEFT]=0)"

 

[boblarson]

Doesn't that filter show only the data I don't want to show? Or does it hide the data that is either ([EXPIREDDATE]< Date() AND [LASLEFT] = 0)
or
([REMAINING] = 0 AND [LASLEFT]=0)"

Oh, I had it backwards did I? No problem - an easy fix to exclude those:

 Me.Filter = "([EXPIREDDATE][B][COLOR=red]>=[/COLOR][/B] Date() AND [LASLEFT] [COLOR=red][B]<>[/B][/COLOR] 0) OR ([REMAINING] [COLOR=red][B]<>[/B][/COLOR] 0 AND [LASLEFT] [B][COLOR=red]<>[/COLOR][/B] 0)"
Me.FilterOn = True

 

[Dunnopat]

as long as LASLEFT is other than 0, the row must be shown.
Out of those where LASLEFT is 0, the rows where REMAINING is more than 0 AND it's not expired, are shown.

Oh, I had it backwards did I? No problem - an easy fix to exclude those:

 Me.Filter = "([EXPIREDDATE][B][COLOR=red]>=[/COLOR][/B] Date() AND [LASLEFT] [COLOR=red][B]<>[/B][/COLOR] 0) OR ([REMAINING] [COLOR=red][B]<>[/B][/COLOR] 0 AND [LASLEFT] [B][COLOR=red]<>[/COLOR][/B] 0)"
Me.FilterOn = True

Underlined data needs to be shown
Bold data is shown if I use that filter. (unless I understood wrong)
expired, lasleft 1, remaining 0
expired, lasleft 1, remaining 1
expired, lasleft 0, remaining 0
expired, lasleft 0, remaining 1
nonexpired, lasleft 1, remaining 0
nonexpired, lasleft 1, remaining 1
nonexpired, lasleft 0 , remaining 1
nonexpired, lasleft 0 , remaining 0


So unless I understood wrong (I got fever so It's very possible), that filter doesn't work right.

I think it took me 20 minutes to write this post.

 

[boblarson]

Okay, sometimes we have to do various iterations to get what we need. Your explanation I THINK should help. Try this instead:

Me.Filter = "([LASLEFT] <> 0) OR ([LASLEFT] = 0 AND [EXPIREDDATE][COLOR=black]>=[/COLOR] Date() AND [REMAINING] [COLOR=black]<>[/COLOR] 0)"
Me.FilterOn = True

 

[Dunnopat]

Okay, sometimes we have to do various iterations to get what we need. Your explanation I THINK should help. Try this instead:

Me.Filter = "([LASLEFT] <> 0) OR ([LASLEFT] = 0 AND [EXPIREDDATE][COLOR=black]>=[/COLOR] Date() AND [REMAINING] [COLOR=black]<>[/COLOR] 0)"
Me.FilterOn = True

Thanks a lot. It was that simple but somehow I just couldn't get it.

It seems that my mind got much clearer too. I fixed some random problems that annoyed me in the past. I never figured out how to fix them before.

 

[boblarson]

Cool, glad we could help.