JS-Q3: Filling multiple blank fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack Sheet - 31 Oct 2005 12:08 GMT

Hi all
Re. Access 97.
I have a table containing a lot of records. In one field some of the
entries are blank, ie "null" entries.
I wish to replace all instances of blank records with the text string
"Other".
I tried to use Edit/Replace, but it requires a positive (non-"null") entry
in the "Find what" field of the Find/Replace dialog box before it will
proceed. I tried using two adjacent double-quotes but that did not help.
I tried sorting the database on that field so that all of the null entries
were grouped together, with the intention of blocking several records for a
single "paste", but it does not seem to let me block more than one record at
a time (without blocking the entire record rather than just one field).
Doubtless what I want can be done by VBA, but I feel there must be an easier
way.

Thanks

PS: please ignore text string "JS-Q3" in subject header.

Reply to this Message

Jack Sheet - 31 Oct 2005 12:50 GMT

Had another attempt:
Never ever having written a macro in Access before I tried the effect of the
following:

Sub Fill_fields()
With TableDefs("T_Data") 'DOES NOT COMPILE
For Each record In .records
If Field("PAYE").value Is Nothing Then Field("PAYE").value = "None"
Next 'record
End With 'TableDefs("T_Data")
End Sub 'Fill_fields()

I was only guessing at the syntax to identify a data table, and was not
surprised that it bombed out at first attempt. Trouble is I am having
difficulty finding my way around the object browser, where I would normally
expect to find something to help.

> Hi all
> Re. Access 97.
[quoted text clipped - 16 lines]
>
> PS: please ignore text string "JS-Q3" in subject header.

Reply to this Message

Van T. Dinh - 31 Oct 2005 14:05 GMT

Try a simple Update Query with SQL String like:

UPDATE [YourTable]
SET [YourField] = "Other"
WHERE [YourField] Is Null

assuming that [YourField] is of Text data type.

Signature

HTH
Van T. Dinh
MVP (Access)

> Hi all
> Re. Access 97.
[quoted text clipped - 16 lines]
>
> PS: please ignore text string "JS-Q3" in subject header.

Reply to this Message

Jack Sheet - 31 Oct 2005 14:15 GMT

Worked like a dream. Thanks.
I couldn't see how to set it up with SQL string but seemed to manage the
simple by using Is Null as the criteria.
Never did an update query before. Looks like I will be using it a lot, now.

> Try a simple Update Query with SQL String like:
>
[quoted text clipped - 25 lines]
>>
>> PS: please ignore text string "JS-Q3" in subject header.

Reply to this Message

Van T. Dinh - 31 Oct 2005 15:00 GMT

In the DesignView of your Query, use the first CommandBar ComboBox in the
Query ToolBar to select the View you want. There are a number of different
views (depending on the Access version) but the SQL View will give the SQL
String of your Query.

You can also use the Menu View / SQL View ...

Signature

HTH
Van T. Dinh
MVP (Access)

> Worked like a dream. Thanks.
> I couldn't see how to set it up with SQL string but seemed to manage the
> simple by using Is Null as the criteria.
> Never did an update query before. Looks like I will be using it a lot,
> now.

Reply to this Message

Jack Sheet - 31 Oct 2005 15:27 GMT

Thanks again

> In the DesignView of your Query, use the first CommandBar ComboBox in the
> Query ToolBar to select the View you want. There are a number of
[quoted text clipped - 8 lines]
>> Never did an update query before. Looks like I will be using it a lot,
>> now.

Reply to this Message