Form.Filter Syntax

[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]

"Clarificat ion: 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. (frmsubAdminEdi t 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 "frmsubDailyOrd ers" 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.