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

"Is Null or Is Not Null? That is the question...

P: 4
First of, Hey there :).

I've been reading a few threads on Access and whatnot, but I cant seem to find the answer I need.

First off, I am a newbie when it comes to computers, so bear with me please.
I am using Access 2003, currently creating a database in order to tranfer our information from our current database, Approach.

I am currently self-taught; books, guides, etc... feeding and absorbing as much as I can but I am stuck now, so I figured I'd ask here and see if I can find myself a tech guy who can help me out ;-).

I have gone through creating tables, queries and forms. I already exported some of information from Approach to Access, so I could have something to work with. Im trying to figure out my "filter and search" possibilities, but when I click on the field that I want to filter and click on the downward-pointing arrow, it gives me two options.. "Is Null" and "Is Not Null"...

Now... thats where I am "stuck" now, it may be something very simple but I rather ask, I saw in Office 2003 for Dummies, that I should see some options, but it doesnt say what to do in case of the above. Tried doing some searchs on google and whatnot but didnt help much.

What steps do I take from here? Did I miss a step along the way or am I supposed to pick one of them and save?

Any advice would be appreciated, hope to hear from anyone soon!
Thanks in Advance.
Nov 21 '06 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I can't reproduce your problem.
How are you getting to the place where you have these options of Null on Not Null?

I suspect that they are just pre-defined opotions and you can add others (Like 'Blah Blah *') if you want to.
Nov 21 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I assume you are talking about filtering datasheet forms.
Is the form bound to a table or query?
In design view is the textbox of the field you are trying to filter on bound to a field in a table or query?

It sounds like you are trying to filter on a control that isn't bound to any field so the only choices available are is null and is not null.



First of, Hey there :).

I've been reading a few threads on Access and whatnot, but I cant seem to find the answer I need.

First off, I am a newbie when it comes to computers, so bear with me please.
I am using Access 2003, currently creating a database in order to tranfer our information from our current database, Approach.

I am currently self-taught; books, guides, etc... feeding and absorbing as much as I can but I am stuck now, so I figured I'd ask here and see if I can find myself a tech guy who can help me out ;-).

I have gone through creating tables, queries and forms. I already exported some of information from Approach to Access, so I could have something to work with. Im trying to figure out my "filter and search" possibilities, but when I click on the field that I want to filter and click on the downward-pointing arrow, it gives me two options.. "Is Null" and "Is Not Null"...

Now... thats where I am "stuck" now, it may be something very simple but I rather ask, I saw in Office 2003 for Dummies, that I should see some options, but it doesnt say what to do in case of the above. Tried doing some searchs on google and whatnot but didnt help much.

What steps do I take from here? Did I miss a step along the way or am I supposed to pick one of them and save?

Any advice would be appreciated, hope to hear from anyone soon!
Thanks in Advance.
Nov 21 '06 #3

P: 4
Im sorry, this is strange for me, first time posting in this sort of forum...

I assume you are talking about filtering datasheet forms

Yes, filtering both datasheets and in form..

Is the form bound to a table or query?

If by bound, you mean "connected" or reproduced from a table/query, then yes it is. I created the form from the query which was created from the table, as instructed.

In design view is the textbox of the field you are trying to filter on bound to a field in a table or query?

How do I check that?

How are you getting to the place where you have these options of Null on Not Null?

Okay- Open Form> choose Records> Filter by Form> click on the field you want to use as your filter> click on the downward pointing arrow> and here... is where im supposed to see a pull down list, in essence, if I click on field State, I should have a list of states... instead, I have the two options.. Is Null is not Null..

According to the guides Im working with, I should be seeing a list... but Im not thus the reason Im stuck atm.

It sounds like you are trying to filter on a control that isn't bound to any field so the only choices available are is null and is not null

I think so too.... any advice? Thanks for the help :)
Nov 22 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534

In design view is the textbox of the field you are trying to filter on bound to a field in a table or query?

How do I check that?

It sounds like you are trying to filter on a control that isn't bound to any field so the only choices available are is null and is not null

I think so too.... any advice? Thanks for the help :)
In form design view, click on the textbox that you are trying to filter on. Open the properties window. Under the data tab there is a property called control source. Does this contain the name of the field from the table that you expect to find?
Nov 22 '06 #5

P: 4
In form design view, click on the textbox that you are trying to filter on. Open the properties window. Under the data tab there is a property called control source. Does this contain the name of the field from the table that you expect to find?
Yes it does.. for example, Design view>form view>click on field records_city>properties>data>control source it says records_city
Nov 22 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes it does.. for example, Design view>form view>click on field records_city>properties>data>control source it says records_city
OK, if you run the query behind the table on its own, is there any values entered in the records_city column.
Nov 22 '06 #7

P: 4
OK, if you run the query behind the table on its own, is there any values entered in the records_city column.
Yes there are values, the information is already in place.
Nov 27 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Yes there are values, the information is already in place.
I don't know what the problem is.

I'm going to ask someone who knows more about filters to have a look at this question.

Mary
Nov 27 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Ok it seems that you've entered in filter by form option...

There appears this options where the information of the respective field is more than a tousand!

If that is less than tousand you can see the list of all kind of things in the respective column...

Maybe your column isn't important and it isn't filled everywhere with data this is the second reason to aapear those options in this combo box for a field!

But this is a form that tries to facilitate you filter your information!

What are you wanting from this form filter?

To be like Excel Filter? Ah no this isn't Excel!

But however to take abilities on filtering you can do the following:

Click on a part of field that contans some letters, select the wanted letters and click the right button of the mouse... Choose filter by selection...

Than go to this form filter watch in the filtered field and remember the way how it was filtered!

So using this tip u can lern filtering your data and use those filters in a query!

:)
Nov 27 '06 #10

nico5038
Expert 2.5K+
P: 3,072
I would start with the "normal" filtering from the right-click popup menu.
Just check this description I give my users:
http://www.geocities.com/nico5038/Ri...nstruction.zip

The "filter by form" is more versatile, but I see it as too difficult for the common user(s). Basically it allows you to fill out the Criteria row from the graphical query editor and by pressing the "OR" tab bottom left you can even add an OR relation.
This is however better controlled in a query where you can see all criteria at a glance.

The "Is Null" or "NOT", is indeed caused by too many hits, but you'll be able to put in every criteria needed anyway.

Nic;o)
Nov 27 '06 #11

P: 1
I had the same problem and believe I'm doing the same thing as you were/are. I found your post when I got the same "Is Null" and "Is Not Null" in my dropdown. The fix was at the Microsoft site. In short, your filter column has more values than defined in the drop-down box limit specified in Tools->Options->Edit. Complete text here: http://office.microsoft.com/en-us/access/HA100378391033.aspx#1c

Excerpt:
If you see only the values Is Null and Is Not Null displayed when you click the drop-down arrow for a field, or if there is no drop-down arrow, do the following to display the actual field values:Verify that the data type for the field is not Memo, Hyperlink, Yes/No, or OLE Object. Fields of these data types cannot be made to display field values on the Filter By Form object tab. Multivalued fields similarly cannot display values on the Filter By Form object tab.
If the number of records read is more than the number specified in the Default max records box in the Filter lookup options for Database section in the Access Options dialog box, Access doesn't display [any] values for that field on the list.


First of, Hey there :).

I have gone through creating tables, queries and forms. I already exported some of information from Approach to Access, so I could have something to work with. Im trying to figure out my "filter and search" possibilities, but when I click on the field that I want to filter and click on the downward-pointing arrow, it gives me two options.. "Is Null" and "Is Not Null"...

Now... thats where I am "stuck" now, it may be something very simple but I rather ask, I saw in Office 2003 for Dummies, that I should see some options, but it doesnt say what to do in case of the above. Tried doing some searchs on google and whatnot but didnt help much.

What steps do I take from here? Did I miss a step along the way or am I supposed to pick one of them and save?

Any advice would be appreciated, hope to hear from anyone soon!
Thanks in Advance.
Sep 17 '07 #12

P: 88
I know this is a really old thread but I happened upon it while looking for an answer to the same issue. I have a form that when you try to do a filter by form on the data it only was giving the options for "is null" or "is not null." All my fields were also bound to a table.

After messing around a bit I found that there is a property under the text box's Data tab that says "Filter Lookup." I set this property's value to "Always" and all my drop downs were suddenly populated with values.

I don't know if this applies, but before I started using a form for my data entry I would use the "Fitler by Form" when directly in my table. At first I was getting the same "is null" or "is not null" values. I had to go into the table design and designate a combo box as the display control on the "Lookup" tab. I then set the the "Row/Source Type" to "Table/Query" and created a query that would grab a DISTINCT value for each value of that field that appeard in the table.

I don't know if you have to do both to get it to work when you are filtering by form on a datasheet form, but I had my table set up this way and the filter by form worked from within the table itself. But in order to get the filter by form to work on my datasheet I had to change the filter lookup property of the text box.
Oct 1 '07 #13

Post your reply

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