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!

[Micron]

query criteria - WHERE IS NULL [NEWDATA] Suggest you try this on a copy of your table first.
Your append to the database table should prevent this in the first place.

[Buns1976]

I would actually prefer that the Append Query "prevent this in the first place" so I must be doing something wrong there? I think the issue is that in Excel column A3:A71 we have
a date that is copied down from Cell A2 because we are never sure how many rows we will need. One day it may be 70, the next it may be 50, and the next 60 depending on whether
or not we received inventory.

query criteria - WHERE IS NULL [NEWDATA] Suggest you try this on a copy of your table first.
Your append to the database table should prevent this in the first place.

[Micron]

2 approaches
- use a different column to provide the actual range of data rows ; one that isn't full of unimportant values
- use dynamic named range in Excel. The range is as large as the rows/columns that contain values. But put range at sheet level, not workbook. Much more adaptive but more difficult to get into Access. Automation is one way. Transferspreadsheet function is another but I can't recall from which application (excel or access) in order to be able to use a range in that method.

[Buns1976]

Micron
I am getting an error "You have entered an Operand without an Operator". Sorry very new to Access.

Thank you

[Micron]

As oft said, post what you tried. "Doesn't work" doesn't help. If I was a mind reader I wouldn't get into so much trouble with the wife!

[Buns1976]

Micron,

As indicated in my most recent post "You have entered an Operand without an Operator" and I'm not sure where to go with that?
Obviously missing something in the equation literally! As previously mentioned I am very new to Access and its pretty much all
Greek to me. No offense intended!

 WHERE IS NULL [NEWDATA] 

[orange]

Go to your query; design view; SQL view
Copy all of the SQL
Paste that SQL in your post.

We need some context for the post.

[ArviLaanemets]

Foolproofest way I use to get a query from Excel:

Define fixed (not dynamic) range with lot of rows (20 000 or 50 000 or whatever);
Base the query on this fixed range, with clause "WHERE SomeField Is Not Null";
Add additional conditions when they are needed.