Access Query

B

boogaloo

Registered User
Messages
149
hi all,

I have a form in Access which I'm trying to filter. Basically, I have approx 10 fields, but not all of them are completely filled in for all records. Example, say a first name is missing, or a telephone number is missing etc.
If I try and filter out any records which have blank fields, how do I do it?

I can filter ones that have '0' in fields, but what do I use for blank space?

thanks!
 
That gives me the ones that are not blank, but I want the records that are blank. So should ="" work?
 
boogaloo said:
If I try and filter out any records which have blank fields, how do I do it?
Click to expand...
From the above I assumed that you were trying to exclude those with blank fields. However you seem to want to do the opposite. Try field = "" so.
 
Do you want to return records where the value of a field is NULL or where it is blank?
 
sorry for confusing matters. I want to get all the records that have nothing typed in to a field.

field = "" doesn't seem to work for some reason, but I think ajapale might be on to something with the 'null' idea.

thanks very much for your help, will let you know what works.


cheers.
 

You need to use the IsNull function, i.e.

select * from tablename where isnull(field1)

to find all records where field1 is not entered

or:

Not IsNull(field1)

if you are trying to get records where data WAS entered.


You can't simply say "Where field1 = Null" (or "Field1 <> Null") - you will always get Null for this comparison.



 
Ok, here is what I'm doing. Go to records, filter, advanced filter.


Come up with a table.

I'm putting the field name in as FirstName
I'm leaving the Sort box blank
Criteria: ???

what exactly do I put in to criteria to get a list of all the records where FirstName field is blank?

I'm a bit confused!

thanks!
 
In 'field' put:
IsNull([Firstname])

under the same column, for criteria, put:
true

It's the same query as MonsieurBond suggested.

 
You must log in or register to reply here.
Menu
Log in
Register