Access: Updating Null Fields with values

[bmaz]

I've received a csv file from a client that I've imported into Access 2010 in order to manipulate it into a format useful for another program I use.

A number of records contain what appear to be null fields that I'd like to set to default values, as they'll cause me grief down the track if they are left as nulls.

I've tried setting up an update query to set the default values for the fields of those records that appear to have null values.

The fields in the file that I want to change are called RD, YLD, CV, IM, Ash, VM, FC and TS.

If RD is null the rest are as well. So I've setup the Criteria for the Update Query using isNull("rd") which produces the following SQL.

UPDATE [BlockModel Wash1p3] SET [BlockModel Wash1p3].RD = 1.8, [BlockModel Wash1p3].YLD = 0, [BlockModel Wash1p3].CV = 1, [BlockModel Wash1p3].IM = 2, [BlockModel Wash1p3].Ash = 90, [BlockModel Wash1p3].VM = 5, [BlockModel Wash1p3].FC = 3, [BlockModel Wash1p3].TS = 1
WHERE ((([BlockModel Wash1p3].RD)=IsNull("rd")));

The first time I ran this query it said it was going to change approximately 3000 records (out of 2.7 million). However when I look back at the dataview for the BlockModel Wash1p3 table none of the records with null values have values in the fields. (ie they still appear to be nulls)

Running the same query again says it's going to update 0 records.



Any ideas what I'm doing wrong?

[ajetrumpet]

one of 2 things is probably happening here:

1) the fields are "" instead of NULL.
2) your query should read:

where CRITERIA IS NULL

instead of using "isnull()". That expression is vb only.

[bmaz]

Thanks Adam.

I managed to klutz my way around to a solution using NZ([RD]) and setting the criteria to 0.

I'm fairly sure the fields were NULLs because that's the error I got when the VB script I'd written to do some calcs on the fields died on me.

I'll give your answer a go as well though. My knowledge of Access is fairly rudimentary. So happy to learn anything new

Cheers
Ben