JS-Q3: Filling multiple blank fields

[Jack Sheet]

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.

 

[Jack Sheet]

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.

 

[Van T. Dinh]

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.

 

[Jack Sheet]

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:

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

assuming that [YourField] is of Text data type.

--
HTH
Van T. Dinh
MVP (Access)

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.

 

[Van T. Dinh]

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 ...

 

[Jack Sheet]

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
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 ...