By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,445 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Form.Filter Syntax

P: 91
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.

Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "ShippedDate=#" & txtDate.Value & "#"
May 10 '10 #1

✓ answered by Megalog

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

Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[PerformanceID] Is Null"

Share this Question
Share on Google+
28 Replies


Megalog
Expert 100+
P: 378
Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[ShippedDate] = #" & txtDate.Value & "# AND [PerformanceID] Is Null"
May 10 '10 #2

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

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.FilterOn = True
May 10 '10 #3

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

Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[PerformanceID] Is Null"
May 10 '10 #4

P: 91
Thanks that worked!

On a similar note, how would I get a filter to display only records after a specific date? I've tried:
Expand|Select|Wrap|Line Numbers
  1. 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
May 10 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
No. What you have should be fine. I always use 4-digit years myself, but it should work that way nevertheless.
May 10 '10 #6

P: 91
It doesnt seem to be working to well, I will get entries from 05/03/2010 but not 05/10/2010
May 11 '10 #7

Megalog
Expert 100+
P: 378
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.
May 11 '10 #8

P: 91
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:
Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[ShippedDate] > #01/01/2010# AND [ShippedDate] <= Date() AND [PerformanceID] Is Null"
This however does work:
Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[ShippedDate] > #01/01/2010# AND [ShippedDate] <= Date()"
May 11 '10 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 11 '10 #10

NeoPa
Expert Mod 15k+
P: 31,494
This however does work:
Do the missing records have a [PerformanceID] value?

Another point - You can use the construct :
Expand|Select|Wrap|Line Numbers
  1. [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 ;)
May 11 '10 #11

P: 91
@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?
May 11 '10 #12

P: 91
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!
May 11 '10 #13

Megalog
Expert 100+
P: 378
@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
May 11 '10 #14

NeoPa
Expert Mod 15k+
P: 31,494
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?
May 11 '10 #15

NeoPa
Expert Mod 15k+
P: 31,494
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?
May 11 '10 #16

P: 91
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
May 11 '10 #17

Megalog
Expert 100+
P: 378
"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.
May 11 '10 #18

P: 91
Sorry that was wrong, I misread what was typed
May 11 '10 #19

Megalog
Expert 100+
P: 378
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)
May 11 '10 #20

P: 91
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.
May 11 '10 #21

Megalog
Expert 100+
P: 378
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)
Expand|Select|Wrap|Line Numbers
  1. frmsubAdminEdit.Form.Filter = "[ShippedDate] Between #01/01/2010# And Date() AND [PerformanceID] Is Null"
Filter 2: (Date range only)
Expand|Select|Wrap|Line Numbers
  1. 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?
May 11 '10 #22

P: 91
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.
May 12 '10 #23

Megalog
Expert 100+
P: 378
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.
May 12 '10 #24

P: 91
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.
May 12 '10 #25

Megalog
Expert 100+
P: 378
Try this:

Filter 3: Date Ranges & Null/Empty Check
Expand|Select|Wrap|Line Numbers
  1. 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'.
May 12 '10 #26

NeoPa
Expert Mod 15k+
P: 31,494
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.
May 13 '10 #27

P: 91
Thanks, that one worked Megalog!

Thanks for both of your guys' help I know I was a bit confusing at times.
May 14 '10 #28

Megalog
Expert 100+
P: 378
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.
May 14 '10 #29

Post your reply

Sign in to post your reply or Sign up for a free account.