How do I delete the contents of a selection of cells in Access 2010?

In Access 2010, if I select a subset of the cells in a column, the Delete key does nothing, and there is no Delete item on the context menu.

Is there some trick to clearing the contents of a selection of cells in Access 2010, or is this a glaring oversight on the part of Microsoft?

microsoft-access

raven

Posted 2010-09-28T19:41:10.627

Reputation: 5 135

Access is not a spreadsheet and expecting it to behave like one will always lead you into problems. – David W. Fenton – 2010-09-29T00:15:37.137

Answers

If you were using Excel, this solution would work:

Try pressing CTRL and the Minus key simultaneously, or Right Click and choose Delete.

You'll have to specify whether the remaining cells get shifted left or up when the selected cells are deleted.

The way you phrased your question, I thought you were using Excel, not Access.

I think the issue is that Access is fundamentally different from Excel. Even though an Access table looks like a spreadsheet, is not a spreadsheet of cells... it is a table of rows and columns. You can delete entire rows or entire columns (fields), but you can't delete "cells" in the middle of a column or row like you do in Excel. In many cases, you can erase the data in a given set of row / column intersections, but you'd have to do them one row at a time. (BTW, deleting "cell" data in Access really means you are setting the value to NULL, which in database terminology means there is no value stored.)

I hope this helps!

stewartwb

Posted 2010-09-28T19:41:10.627

Reputation: 302

Ctrl+- does nothing and, as I said, there is no Delete item on the context menu. – raven – 2010-09-28T20:36:41.883

UPDATE query The proper way to delete the value stored in a column for a given set of rows is to use an UPDATE query, which you can do using the Query Designer. I am a SQL Server DBA, and I write SQL code instead of using designers. The SQL statement would be something like UPDATE table SET fieldname = NULL WHERE KeyField IN (comma separated list of IDs) – stewartwb – 2010-09-28T20:41:15.733

My question was misleading. Selecting a column and hitting delete removes the column, it doesn't clear the contents (I edited my question). I was thinking in terms of spreadsheet, not database. I was looking for a way to do it through the UI, SQL never crossed my mind, but that will do it. Thanks. – raven – 2010-09-28T22:02:38.013

This will work: Copy the same number of empty cells from a nearby field. Select all the cells you want to clear and paste the cleared cells.

KalahariKev

Posted 2010-09-28T19:41:10.627

Reputation: 11

Easy work around if you just want to clear the cells, not remove them:

Select all applicable rows/columns. (Might help if you sort the data so that all the cells you want to clear are in adjacent rows/columns.) Copy (Ctrl+C) these.
Open Excel. Paste (Ctrl+V) copied cells in. Select rows/columns/cells you want cleared (I had a number of cells that had a certain text string in them so I just did find/replace to remove what I wanted gone).
Copy those same cells in Excel. Go back to Access, you should still have the right cells selected, so you can just paste the corrected ones back it. Probably a good idea to save before you try this as it gives a warning about not being able to undo changes (though this has never caused a problem for me.)

dylan murphy

Posted 2010-09-28T19:41:10.627

Reputation: 171