Cleaning up Null values within a table

[Birky]

Do to multiple iterations of data contained in my Event_Log table I now find myself having to update all entries within my Element_Name column that are = “ “ to a Null value. This is necessary for when I am running reports and trying to group by this filed I am getting erroneous entries due to some values being null and some = to “ “. What is the best way to update these “ “ entries to Null? Please note I have several thousand of these entries within my table and doing one at a time would drive a person insane.

Any help would be greatly appreciated.

Birky

Event_Log Table consists of the following columns:

Event_Log_ID
Date_Recorded
Date_Installed
Remedy_AR_Number
Instance
Schema
Project_Name
Element_Name
Version
Comment

[nico5038]

In general I use the NZ() function to change Nulls into a detectable value for a query.
Thus you can exclude empty field by using:
WHERE NZ(TextField,"") = ""

Looks easier as updating, although a simple update query can be used like:

UPDATE tblX SET fieldname = "" where fieldname = Null;

Nic;o)

[Birky]

How would I implement the Nz function within the below select?

SELECT Event_Log.Instance, Event_Log.Project_Name, Event_Log.Element_Name, Max(Event_Log.Version) AS MaxOfVersion
FROM Event_Log
GROUP BY Event_Log.Instance, Event_Log.Project_Name, Event_Log.Element_Name;

[nico5038]

You need to check in the table definition which fields can contain Nulls.
When that's for instance Project_Name, than use:

SELECT Event_Log.Instance, NZ(Event_Log.Project_Name), Event_Log.Element_Name, Max(Event_Log.Version) AS MaxOfVersion
FROM Event_Log
WHERE NZ(Event_Log.Project_Name) <> ""
GROUP BY Event_Log.Instance, NZ(Event_Log.Project_Name), Event_Log.Element_Name;

The WHERE will make Access to skip empty and Null Projectnames.

Nic;o)