Filtering (Access 2002)

Topic

New Reply

I have two filters that I am using on a form, and each works perfectly on their own.

They are enacted by using a click event on different buttons.

DoCmd.ApplyFilter , “JWord Like [Enter All or Part of Key Word]”

DoCmd.ApplyFilter , “JDate >[Enter Date]”

I would like to add a third to combine the two, so that I get a filter where both criteria are met.

So far I’ve tried various combinations but failed to get it to work. The help files don’t seem to be much use.

Can anyone help please.

Thanks

Reply | Quote

Replies

If you want to return all records that meet *both* criteria, put AND between the criteria. Try:

DoCmd.ApplyFilter , “JWord Like [Enter All or Part of Key Word] AND JDate >[Enter Date]”

(Similarly, if you want to return all records that meet *at least one* of the criteria, put OR between the criteria)

Reply | Quote

Thanks

I had tried the AND outside the quotes.

It is obvious after it is explained to me.

Thanks

Colin

Reply | Quote

Taking this a stage further, is there a way to avoid keying the “/” in the date.

The data field in the original table uses an input mask of 99/99/99, to keep keystrokes down, and speed up.

Ideally I’d like to do the same for this filter.

I’ve tried setting the IputMask property, before I apply the filter, but I get a message saying “Run Time error 438, Object doesn’t support this property or method”

Ta

Colin

Reply | Quote

Either they have to key in the slashes in the date or you have to wrap the criteria expression in a format function to get the desired result. You can’t use input masks except on fields or controls themselves.

Reply | Quote

Presumably since there are two parameters in this expression (see below), one string and one date, then wrapping the whole thing in a format is impossible.

“JWord Like [Enter All or Part of Key Word]& ‘*’ AND JDate >[Enter Date]”

Thanks anyway.

Colin

Reply | Quote

No, not the whole thing, that parameter. Unfortunately, I don’t think it will work, though. Without the slashes to parse the value, Access simply doesn’t know how to interpret a number as a date. If it gets it right, it’s through sheer dumb luck. You might be better served by expanded the parameter prompt like this:

JDate > [Enter Date dd/mm/yy]

Reply | Quote

Thanks but this one didn’t work.

Colin

Reply | Quote

I would use two textboxes on the form where the user can enter criteria to be used for filter. Then you could use an input mask for the date criteria textbox. The Apply Filter expression would refer to the value of the textbox (or textboxes). This might simplify things.

Reply | Quote

I’ll give this a try as well.

Thanks

Reply | Quote

I haven’t really tested this, but you might try

“… AND JDate > DateSerial(Right([Enter Date], 2), Left([Enter Date], 2), Mid([Enter Date], 3, 2)”

This will only work if your users *always* enter the date as a six digit string in the form “mmddyy”, like 070802. It’ll fail if they omit a leading zero or if they enter the year as 2002.

Added by HansV (July 8, 15:38 UTC):

Colin correctly pointed out that I forgot one closing parenthesis, so it should have been

“… AND JDate > DateSerial(Right([Enter Date], 2), Left([Enter Date], 2), Mid([Enter Date], 3, 2))”

Reply | Quote

Yes that worked.

As the number of users is small, and I can train accordingly, I can live with the reqiuirement to enter 6 digit dates.

And best of all, this works!

Thanks

PS for anyone copying Hans’ solution, the code needs an extra ) at the end before the “

Reply | Quote