Need help with a delete query

[Buns1976]

Hi Everyone,

I am in need of some help on a delete query in an Access table.

I have a linked Excel table which is a worksheet that is filled out daily. That worksheet has several columns that are
deleted daily with a macro so we can enter new data. What remains are various formulas in various columns. We have
row2:row71. Not all rows are populated daily but they still show some columns which are waiting on population.

Once the Excel form is complete, it is saved and then we append to a table in Access. The issue is that the rows we didn't
populate in Excel still have enough data in them from the formulas in Excel to create a record in the Access table creating clutter and useless information.

With that said what I would like to do is create a Delete Query that would delete any records with a NULL value in a given field
so for example if Field "NEWDATA" is null or empty then delete that record.

Thank you!

 

[xenou]

If NewData is string/text data then the query for deletion would be something like:

delete * from [TABLE_XYZ] where ([NewData] is null or [NewData] = "")

 

[Buns1976]

xenou ,

Where an I putting this? In criteria? If so I first received an error on invalid use of parenthesis. After I removed those the query DOES NOT delete the records.
There are both number and text fields in the table!

Thank you!

If NewData is string/text data then the query for deletion would be something like:

delete * from [TABLE_XYZ] where ([NewData] is null or [NewData] = "")

If NewData is string/text data then the query for deletion would be something like:

delete * from [TABLE_XYZ] where ([NewData] is null or [NewData] = "")

 

[xenou]

There are both number and text fields in the table!

The only field that matters is the one that is used in the criteria (field "NewData"). In other words, the delete query is deleting the entire record but only checking criteria in the field "NewData". The reason why it matters is because I'm using a Null or Blank criteria, where my blank criteria is an empty string (which is text). However, we could make it work for text or numbers so that the data type doesn't matter:

delete * from [TABLE_XYZ] where ([NewData] is null or len([NewData]) = 0)

Where an I putting this?

This is a raw sql query text so you put it into a new query in SQL view. 1) You click the create new query button (not with the query wizard though). 2) Then you close the dialog for selecting tables. 3) Then hit the SQL View Option from the View menu in the top left (more or less), 4) then in sql view you delete what is there already, and paste in your sql text. There should be a number of you tube videos as examples if you google "msaccess youtube create query in sql view"

Where an I putting this?

Please note you do not literallly paste this or put it anywhere. You first have to change "Table_XYZ" to your actual table name. If NewData is not your actual field name then that must be changed too.

 

[Buns1976]

xenou I got it! Wasn't sure how you wanted the query set up. Works like a charm!

Thanks a Bunch!!

The only field that matters is the one that is used in the criteria (field "NewData"). In other words, the delete query is deleting the entire record but only checking criteria in the field "NewData". The reason why it matters is because I'm using a Null or Blank criteria, where my blank criteria is an empty string (which is text). However, we could make it work for text or numbers so that the data type doesn't matter:

delete * from [TABLE_XYZ] where ([NewData] is null or len([NewData]) = 0)

This is a raw sql query text so you put it into a new query in SQL view. 1) You click the create new query button (not with the query wizard though). 2) Then you close the dialog for selecting tables. 3) Then hit the SQL View Option from the View menu in the top left (more or less), 4) then in sql view you delete what is there already, and paste in your sql text. There should be a number of you tube videos as examples if you google "msaccess youtube create query in sql view"


Please not you do not literallly paste this or put it anywhere. You first have to change "Table_XYZ" to your actual table name. If NewData is not your actual field name then that must be changed too.