Form.Filter Syntax

[matt753]

I want to impliment a button that filters and shows only records where the field "Performanc eID" has nothing in it.

Currently have the following for filtering by date, I know the syntax is similar but I cant seem to get it right.

frmsubAdminEdit.Form.Filter = "ShippedDate=#" & txtDate.Value & "#"

[Megalog]

frmsubAdminEdit.Form.Filter = "[ShippedDate] = #" & txtDate.Value & "# AND [PerformanceID] Is Null"

[matt753]

I was just trying to do the one filter, not both, but that helped with the syntax. I tried:

frmsubAdminEdit.Form.Filter = [PerformanceID] Is Null

But I get the error: Run Time error 2465: cant find the field '|' refferred to in your expression.

I also have this line right after the previous cause I thought you need it:

frmsubAdminEdit.Form.FilterOn = True

[Megalog]

The filter you're passing is a string, which in all cases needs to be encapsulated with double quotes like below:

frmsubAdminEdit.Form.Filter = "[PerformanceID] Is Null"

[matt753]

Thanks that worked!

On a similar note, how would I get a filter to display only records after a specific date? I've tried:

frmsubAdminEdit.Form.Filter = "[ShippedDate] > #01/01/10#"

but that doesnt display all records after that date for some reason. Thinking it has something to do with the > symbol

[NeoPa]

No. What you have should be fine. I always use 4-digit years myself, but it should work that way nevertheless.

[matt753]

It doesnt seem to be working to well, I will get entries from 05/03/2010 but not 05/10/2010

[Megalog]

Is this a subform you're passing a filter to? If so, it may have a parent/child relationship that is limiting the records returned, regardless of the filter you're passing to it.

[matt753]

Yes it is a subform.

Would there be an easier way to do this then? Maybe with SQL or something?

What I need to do is show all records between 01/01/2010 and the current day, where the PerformanceID Is Null. This is the current code that doesnt work:

frmsubAdminEdit.Form.Filter = "[ShippedDate] > #01/01/2010# AND [ShippedDate] <= Date() AND [PerformanceID] Is Null"

This however does work:

frmsubAdminEdit.Form.Filter = "[ShippedDate] > #01/01/2010# AND [ShippedDate] <= Date()"

[NeoPa]

I will get entries from 05/03/2010 but not 05/10/2010

What data do you get when you don't apply the filter?

Remember, that is the set of data your filter is applied to.

[NeoPa]

This however does work:

Do the missing records have a [PerformanceID] value?

Another point - You can use the construct :

[ShippedDate] Between #01/01/2010# And Date()

This is just a neater and more efficient way to do the bit that is already working of course ;)

[matt753]

What data do you get when you don't apply the filter?

Remember, that is the set of data your filter is applied to.

When the filter isn't applied I get all of the records, which are descending by ShippedDate.

I thought it was a problem with the AND's in the filter syntax, does vba had AndAlso or OrElse like VB does?

[matt753]

Do the missing records have a [PerformanceID] value?

Yes

This is just a neater and more efficient way to do the bit that is already working of course ;)

Thanks!

[Megalog]

Do the missing records have a [PerformanceID] value?
Yes

Then Access, like usual, is doing exactly what you told it to do. You're not seeing those records because you're adding "[PerformanceID] Is Null" to the filter.

If you want the opposite, then use Is Not Null

[NeoPa]

Matt,

It's difficult to follow what you mean when you're not explicit, especially as you seem to jump between different situations.

Your post #12 was in response to my post #10, which in turn was a response to your post #7 - referring to your post #5 which is simply comparing the [ShippedDate] field to #01/01/10#. Since this earlier position, you've moved on to discuss a more complex WHERE clause which includes a start and end date, as well as checking [PerformanceID] for Null.

This makes a sensible conversation very difficult (It's naturally somewhat complicated already by trying to do it via a web page).

Can you confirm that simply applying the date filtering (Let's standardise on the Between version to avoid confusion) results in records being absent that are present when there is no filtering?