Show all Blank Records

[access2010]

Could I receive a suggestion on how to have our query show ONLY the records with NO DATA in the company field?

Thanks
Fabiola

 

[Galaxiom]

WHERE tablename.Company Is Null

 

[access2010]

Thank you for your suggestion.

I think that I have done what you suggest, but we do not see only the Blank (empty) Company records

What would you suggest that I try now?

Thanks
Fabiola

 

[prpmtn]

Maybe try this, since this is a text field?

WHERE (((unwanted_Emails.Company)=""));

Hope it helps.

 

[Galaxiom]

Maybe try this, since this is a text field?
WHERE (((unwanted_Emails.Company)=""));

In my experience, a text field in Access cannot hold a null string. Indeed they won't even hold just spaces or trailing spaces. They convert automatically to Null.

However such things are possibilities if it is a linked table to another type of database.

In these cases the usual test is:
WHERE Len(unwanted_Emails.Company & "")= 0

 

[access2010]

Thank you for your suggestions, but no success has been reached

Thanks
Fabiola

 

[Eljefegeneo]

Try this:
SELECT unwanted_Emails.Company, unwanted_Emails.ID
FROM unwanted_Emails
WHERE (((unwanted_Emails.Company)<>""))
ORDER BY unwanted_Emails.Company;

I often have this same problem and this seems to work more often than not.

 

[Galaxiom]

I often have this same problem and this seems to work more often than not.

Yes the records are a zero length string.

Any of these work to select the empty records:

WHERE Company = ""

WHERE Len(Company]) = 0

WHERE Len(Company & "") = 0

(The first two will miss Nulls)

I am curious about where the data came from. I have never been able to enter a ZLS into an Access table using the keyboard.

 

[namliam]

The "proper" or "clean" way to handle both null and zls values is to use the NZ function
NZ(company, "") = ""

 

[access2010]

Thank you all for your suggestions

We want to see all the records GROUPED together that have NO DATA in the field COMPANY so that we can delete all these records (over 1,000 a week)

The charity that I volunteer at has a web site that receives many thousands of visits by robots every week. We think the robots are trying to reach the charities credit card database

About a third of the visits leave the COMPANY FIELD empty or blank. We would like to have a query that groups these empty / blank company records so that we can delete ALL of these records

The suggestions that we have tried do not work, could someone use the Access data base attached and place your working code into this data base and upload the working data base for us

I do appreciate your assistance
Fabiola

 

[access2010]

Thank you all for your suggestions, but, I/we can not get any of the queries to group records that have no data in the Company field

Fabiola

 

[access2010]

Thank you all for your assistance, we now have our queries working the way we want.

 

[access2010]

SELECT unwanted_Emails.Company, unwanted_Emails.ID
FROM unwanted_Emails
WHERE (((unwanted_Emails.Company)=""))
ORDER BY unwanted_Emails.Company;

I hope this code will help others