Null to Zero from External Data

[Guest]

I am new to Access programming, but am familiar with Access programming.

I have a range of cells linked to a Query in Access that produces null
values. I used the the Nz function in Access to convert the null values to
"0" (Ex. Nz([DISCHARGES],0,[DISCHARGES]), but when I refresh the data in
Excel, Excel does not recognize the Nz function.

I tried to write a n If statement into the SQL statement, which accomplishes
the same thing on the Access side, but then when I refresh in Excel, I get a
message saying "too few parameters".

I need for the empty cells to convert to zero for use in functions that
display averages over time.

What is the best way to deal with this? Write code that replaces the empty
cells with zeros? If so, where would I put it?

Thanks in advance.

DEI

 

[Tom Ogilvy]

http://support.microsoft.com/support/kb/articles/q182/7/35.asp
XL97: How to Use the Query Before and AfterRefresh Events

 

[Guest]

Please provide more code specifics.

You mention Excel and Access ..
then refresh in Excel causing issues.....
Nz should work so the issue is somewhere in your process...

 

[Guest]

Sorry for being unclear, I meant to say I am new to EXCEL programming and
familiar with Access programming.

Basically, EXCEL does not recognize the Nz functiion I used in the SQL
statement of the query it is fetching from Access; I need the empty cells to
appear as zeros. The Nz function is a simple function in Access that, when
it encounters a null value, converts it to whatever you specify, ex:
Nz([total],"-") produces a "-" when the total is null.

What I eventually did was add the following code to the Change event, so it
will run after the data is refreshed. I suppose the AfterRefresh event will
work even better:

'-Code converts null values to a zero for a specific range if
cells...determined by
'-which cells already have data.

Dim rc As Long

rc =
Application.WorksheetFunction.CountIf(Workbooks("OCT-2005").Worksheets(8).Range("N7:N38"), "<>")

With Workbooks("OCT-2005").Worksheets(8)
'.Range("L7:M" & 6 + rc).Replace What:=Null, Replacement:="0"

End With

This a limitatiion in automating Excel with Access, unless there is
something in Excel designed to handle blank fields, etc.

Thanks for your replies.

DEI

http://support.microsoft.com/support/kb/articles/q182/7/35.asp
XL97: How to Use the Query Before and AfterRefresh Events

--
Regards,
Tom Ogilvy

I am new to Access programming, but am familiar with Access programming.

I have a range of cells linked to a Query in Access that produces null
values. I used the the Nz function in Access to convert the null values to
"0" (Ex. Nz([DISCHARGES],0,[DISCHARGES]), but when I refresh the data in
Excel, Excel does not recognize the Nz function.

I tried to write a n If statement into the SQL statement, which accomplishes
the same thing on the Access side, but then when I refresh in Excel, I get a
message saying "too few parameters".

I need for the empty cells to convert to zero for use in functions that
display averages over time.

What is the best way to deal with this? Write code that replaces the empty
cells with zeros? If so, where would I put it?

Thanks in advance.

DEI