Query Help ..... PLEASE!

[turtle]

Hello,

I have an MS Access with 5 fields. The second field labeled area. I have about 1000 records out of that only about 400 have an actual value in the second field (area). I want to write a query that will select all the records that have the second field empty and then automatically fill in those empty records with "home" as the area.

So as an overview what I need is a query that will put the word home in the area field if it is blank.

This field is a text value.

Thank You

 

[CT Witter]

Try something like:

Area1: IIf(IsNull([Area]),"home",[Area])

Place this in the Field of the Query Design Window.

HTH,
CT

 

[SydneyGeek]

CT Witter's answer is useful if you want to add another field to a query. However, if you want to permanently modify the data you need an Update query.
Do this:
1. Create a query with just one field: the field you want to update.
2. In the Criteria row of the query grid, below the field with the null data, type Is Null
3. Now, under the Query menu, select Update Query. You'll get another row in the grid called Update. In the null data field, type "Home" (with the quotes)
OK, now to check. Go to Datasheet view. You should see one column with no data -- these are the records with Nulls. Save and close the query.
To run the query, double-click the icon. You will get a warning about updating (or modifying) X number of records. Click OK and you have "Home" in all the blanks.
You can run the query periodically to fill in the blanks, or alternatively you could go into design view in the table, select the field in the top half of the view, and in the bottom enter "Home" as the default value.

HTH
Denis