Need help with a delete query

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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

Board Regular
Joined
Feb 11, 2019
Messages
138
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,692
Office Version
  1. 2013
Platform
  1. Windows
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:

Code:
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.
 
Last edited:

Buns1976

Board Regular
Joined
Feb 11, 2019
Messages
138
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:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,318
Messages
5,527,992
Members
409,796
Latest member
Legion74

This Week's Hot Topics

Top