Form.Filter Syntax

[matt753]

I want to impliment a button that filters and shows only records where the field "PerformanceID" 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]

@NeoPa
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]

@matt753
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?

[NeoPa]

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.

That would be my reading of the situation too, except it may turn out that this snippet of conversation is actually referring correctly to the position in the thread it claims to refer to and, though complicated to follow, refers to the filter which excludes the [PerformanceID] filtering. Confused yet?

[matt753]

Sorry for the confusion, what is stated in post # 9 is what I am trying to do. I kind of changed what I was programming half way through this post cause I realized I could add for functionality.

Clarification: I need to display records where PerformanceID IS NULL, And the dates of those records are between 01/01/2010 and Current Date

[Megalog]

"Clarification: I need to display records where PerformanceID IS NULL, And the dates of those records are between 01/01/2010 and Current Date "

Ok.. and in post #13 you said the missing records HAVE a PerformanceID. So they will not show up if you are filtering for NULL values.

[matt753]

Sorry that was wrong, I misread what was typed

[Megalog]

Ok. Now, is there a Master/Child relationship established between the form and the subform? These are properties of the subform control, not the subform within it. (frmsubAdminEdit is the subform control I'm guessing)

[matt753]

Im thinking there is, however i'm not positive.

In the Properties window for the subform, the Source Object box has "frmsubDailyOrders" in it, but the link child and link master fields are empty.

[Megalog]

Since there's no Master/Child relationship, there isnt any initial filtering happening to the subdata. So, I'm a bit perplexed as to why the filter doesnt work for you, it's all valid. I tested with a sample table and both of these filters work for me:

Filter 1: (Date range and null check)

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

Filter 2: (Date range only)

frmsubAdminEdit.Form.Filter = "[ShippedDate] Between #01/01/2010# And Date()"

Compare the returned results from Filter 1, with what you get with Filter 2, and try to determine where the error exactly is. I have a feeling there may be something else interfering with your results. Is there any event code behind the subform itself?

[matt753]

Yes its one of those small weird things that I cant figue out why its happening.

There is no code behind the subform itself, lots behind the regular form though.

I'll probably just end up not implimenting this feature and just making a "Show records since this date" and have the user look to see if that field is null and make the descision themselves. Still easier than clicking each date individually, was just hoping to combine those two.

[Megalog]

Well there's no reason to give up on a simple feature like this. Did you do as I suggested, compare the results from Filter 1 to Filter 2? Try Filter 2 first (dates only), and apply Filter 1. It should be showing less records with filter 1.

[matt753]

Yes, the date range (Filter 2) works fine, but when you add in the Null check (Filter 1) there are many records missing that are in the date range, and can clearly see have that field Null, but dont show up when running Filter 1.

[Megalog]

Try this:

Filter 3: Date Ranges & Null/Empty Check

frmsubAdminEdit.Form.Filter = "[ShippedDate] Between #01/01/2010# And Date() AND [PerformanceID] & '' = ''"

Another thought.. make sure your control name on the subform for PerformanceID is named something other than 'PerformanceID'.

[NeoPa]

Megalog has shown you a way of filtering for Null or empty string. This may be your problem as a field (or control) with an empty string displays exactly as a field/control with a Null does.

[matt753]

Thanks, that one worked Megalog!

Thanks for both of your guys' help I know I was a bit confusing at times.

[Megalog]

You're welcome Matt, glad we got all your issues resolved. Learning how to deal with empty strings and/or null values can be aggravating sometimes.