How to replace empty cells by NaN or -99

  • Thread starter Thread starter ytramblay
  • Start date Start date
Y

ytramblay

Hi,
I am currently working on huge files imported into Access (1.5 Go in
total splitted in 12 files). It is climatic datasets, and there is
emply cells. i would like to find a easy way to replace all theses
empty cells by a value (NaN or -99) in order to export theses files in
Matlab or to work with C

Thank you very much !
 
First thing first. Access doesn't have cells. That's spreadsheet talk. Rather
Access has fields in records.

Second we need to define what you mean by empty. There are at least three
things which look like empty cells (whoops!), I mean fields in Access: Nulls,
Empty Strings, and Spaces. You may need to check what actually in these
fields. Number and date/time fields can't have empty strings or spaces so
just check them for nulls.

If something like this returns records, it's nulls:
Select count(*)
FROM YourTable
WHERE IsNull(YourField) = True ;

This one returns records with empty strings in a field. Notice that there
isn't a space between the quotation marks:
SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourField="" ;

This one returns records with spaces in it. Notice that there is a space
between the quotation marks plus a wildcard just in case there are multiple
spaces in the field:
SELECT YourTable.*
FROM YourTable
WHERE YourTable.YourField Like " *" ;

Don't be surprised if you find all three in a field!

Here's what you would do to update a field with nulls:
UPDATE YourTable SET YourTable.YourField = "NaN"
WHERE IsNull([YourField])=True;
 
UPDATE tblData SET tblData.FldData = "NaN"
WHERE (((tblData.FldData) Is Null));

HTH

Vlado
 
Back
Top