I am trying to exclude any blank/empty/null records from my query. If I simply add IS NULL as the criteria for the field the blank/empty row still returns.
What other way do I have to exclude this row from my query since IS NULL is not working?
I am trying to exclude any blank/empty/null records from my query. If I simply add IS NULL as the criteria for the field the blank/empty row still returns.
What other way do I have to exclude this row from my query since IS NULL is not working?
To exclude Null values I think you need criteria as:
Not Is Null
maybe I explained poorly. I'm wanting to isolate all the null values so I can run a delete query and remove them from the table. But using IS NULL does not show any blank rows even tho there is one
is null applies to a specified column, not an entire row. With a couple of possible exceptions every table will have a primary key which cannot be null. So suspect there is something you aren't telling us.
Suggest provide a screenshot of your table showing the 'null row' and the sql to your delete query
Could it be that the OP has a ZLS rather than a Null
I'm importing a .csv file into access so at this point we do not have a primary key assigned. I'm working on getting an image uploaded, but when I filter the table on the Address1 line access shows (Blanks) and if I filter on (Blanks) I see the row that I want to delete
image here
https://ibb.co/bNvLw0D
As Bob said, you might have a zero length string, can you try using "" in your criteria, or even better use it in a new field where you trim the address field in case there are any spaces: Select * From YourTable Where Trim ([Address 1]) = "";
Cheers,
ah - that looks like it may be it. What would be the proper VBA Syntax for this?
if I do a direct copy/paste I get the errorCode:DoCmd.RunSQL ("Delete Test1.[Address 1] From Test1 Where (((Test1.[Address 1])=""));")
Run-time error '3075':
Syntax error in string in query expression '(((Test1.[Address 1])="));")
Try
Cheers,Code:CurrentDb.Execute "Delete * From Test1 Where Trim([Address 1])=''; ", dbFailOnError ' note second to last are actually two single quotes
That got it. Thank you kindly