Update Query to Search for Blanks in Certain Column

[ryguy7272]

I am quite proficient with Excel, but relatively new to Access. I suspect
there is an easy way to use an update query to get Access to delete an entire
row if a certain column has blanks, but I don't know how to do this. The
reason for this is that some numbers are double-counted. If items in Column
C in Excel, the FieldName is known as ([Period]) once it is imported to
Access. Can someone offer a solution?


Thanks so much,
Ryan--

 

[Golfinray]

One thing you could try is to simply type in your query criteria at the
bottom Is Null. That will tell you if there are nulls in that column and if
there are you can simply highlight, right click, and delete.

 

[John Spencer]

You use a delete query, not an update query.

DELETE
FROM YourTable
WHERE Period is Null

In query design view
-- Add your table
-- Add the field Period
-- enter Is Null as the criteria
-- Select Query: Delete from the menu
-- Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Jerry Whittle]

First thing is to make a copy of the table or entire database just in case
something goes wrong....

Create a new query based on the table in question with all the fields down
in the grid.

Next put the word Null in the Criteria row under the field in question.

Run the query and see if it returns all the records you want and ONLY the
records that you want to delete. If not, see what is wrong.

If so change the query from Select To Delete. This option should be up on
the menu under Query (unless you are using Access 2007).

Run the delete query.

 

[ryguy7272]

Perfect! Just what I was looking for! Thanks to all three of you!!!
Ryan---
--
RyGuy

You use a delete query, not an update query.

DELETE
FROM YourTable
WHERE Period is Null

In query design view
-- Add your table
-- Add the field Period
-- enter Is Null as the criteria
-- Select Query: Delete from the menu
-- Select Query: Run from the menu

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I am quite proficient with Excel, but relatively new to Access. I suspect
there is an easy way to use an update query to get Access to delete an entire
row if a certain column has blanks, but I don't know how to do this. The
reason for this is that some numbers are double-counted. If items in Column
C in Excel, the FieldName is known as ([Period]) once it is imported to
Access. Can someone offer a solution?


Thanks so much,
Ryan--