Populating 0's in Null Fields

[Dugantrain]

I have a Workbook that I have attached to my Access 2000 database. I have several queries written, all which use the Nz(x,0) function to return a 0 in any Null field and each worksheet in the workbook is attached to a separate query. This workbook has been set to refresh itself on open; however, I keep getting the error message that Nz is unrecognized, when trying to Refresh. Is Nz simply not handled by Excel? If this is the case, I'd like to try to do this on the Excel side; that is, do not alter the Nulls in the Access query, but select an entire column in the Worksheet and set it to autopopulate any Null cells with 0's when Refreshed.

 

[Parra]

We'll something does happen when you Export/Import data from Excel to Access.

Like True or False in Excel when imported to Access are converted to 1 and 0 and vise versa.

 

[dk]

Hi,

A simple one line macro to replace any null values with zeros is this:-

ActiveSheet.QueryTables(1).ResultRange.SpecialCells(xlCellTypeBlanks).Value = 0

Does that help?

 

[Dugantrain]

OK, the code makes sense, where should I put it? In the Workbook code or in each Worksheet where I want 0's populated? And what method/event would this code fall under?

 

[Dugantrain]

Oh, I see, it's a Macro, so I record it and manually call it whenever I need it. This works fine, but ideally I'd like it to automatically populate certain worksheets with 0's.