Macro for Deleting Blank Records (1 Viewer)

[Hey Lucy]

How can i create a macro that will delete blank records but ONLY blanks from a table?

 

[RainLover]

How can you have BLANK records.

It there is nothing there then there is no record.

Are you sure you have blanks or does it just look like that.

 

[Hey Lucy]

Yeah, this one is kind of a weird situation...I am having to import the data into Access from Excel. My client downloads the Excel SS daily from a website. This SS requires many formulas to "clean up" data that are completely necessary to identify duplicates. Anyway, since the amount of records downloaded daily into this SS can vary, the client has asked me to allow space for 200 records, meaning that 200 rows of the SS have to have all the formulas. Now, today there may be 200 records, tomorrow those may be replaced with 50 records. In that case, the last 150 rows of the SS show as blank but actually have formulas in them. These formulas begin with an IF statement that if the linked cell is blank, then leave the current cell blank, but if it's not, then perform the desired function. Because these so-called blank cells are actually NOT blank, when imported, it will import all those rows that don't have actual formula results in them (the "blank" rows). I need to somehow delete the "blank" records that then import into Access, but it needs to be an automated process, not done manually. Maybe I'm going backwards in this and need to have some kind of code to delete those rows while still in the SS, before the import. In any case, those records import into Access and get an ID number assigned to them via the import, so they appear as records in the DB table, but they have no other data than the program-assigned ID.

Does any of this make sense?:banghead:

 

[RainLover]

I think this is what is happening.

The SS has some cells that act as though they have data. Most likely they are ZLS (Zero length strings). When you import them they show as blank reords in the Table.

I assume you are importing using CODE.

You could after the import, delete all records where certain fields are Null or contain a ZLS. This can be done by a Delete query.

You could try setting all your fields in the Table to not allow ZLS. I have never done this amd I don't know what problems it might cause. Most likely it will slow things down as one problem. It would have to check every field for every record.

You could look at deleting Cells in Excel (Manually) before the import.

Do any of these appear to be an option for you?

 

[Hey Lucy]

Thanks! Got it! The delete query worked great! Just what I needed. I'm not using code to import...very, very novice level at VB. I'm doing a macro to import the SS then delete those null records. After that, I have to append to another table, but I will include that in the macro also (if possible). If not, I'll be back to ask how to do it with VB.

Thanks again for your help!

 

[RainLover]

Happy you found a solution.

Did you know that Macros can be converted to VBA if required.

I personally do not work with Macros.

 

[Hey Lucy]

I'm completely new to VB but see that I'm going to have to learn it in this version of Access (2010). Older versions you could basically write the macros as you wanted them but now you're limited to the macro commands in Access. Sigh.

Thanks for your help! I've been developing databases for years, and have always used macros to automate processes for the end user, but this time it's a bit harder and without knowing VBA I feel like I'm stumbling around in the dark. Have been able to write minor code based on what others have helped me with, but still having issues with other things.