cekendricks
asked on
Replacing Nulls with 0
I have an access database which exports data out to an Excel spreadsheet. Under certain conditions there may be Null values exported from the database which results in blank fields in the spreadsheet. I want these blank cells to display 0.00 and have a numeric, not text, data type. I am currently using the following code in Access to format the cells which display numeric values:
Set xlRng = xlWs.Range(xlWs.Cells(2, 12), xlWs.Cells(x, z))
xlRng.NumberFormat = "###,###,###,##0.00;[Red]( ###,###,## #,##0.00)"
Set xlRng = xlWs.Columns("I")
xlRng.NumberFormat = "00"
Set xlRng = xlWs.Columns("J")
xlRng.NumberFormat = "###,###,###,##0;[Red](### ,###,###,# #0)"
Any help would be greatly appreciated
Set xlRng = xlWs.Range(xlWs.Cells(2, 12), xlWs.Cells(x, z))
xlRng.NumberFormat = "###,###,###,##0.00;[Red](
Set xlRng = xlWs.Columns("I")
xlRng.NumberFormat = "00"
Set xlRng = xlWs.Columns("J")
xlRng.NumberFormat = "###,###,###,##0;[Red](###
Any help would be greatly appreciated
ASKER
Yeah I already thought of that, but the problem is that I would have to change the fieldname in the query (ie newFieldName: Nz([FieldName], 0). This would be just too time consuming to do as there are 12 queries (one for each month), with each query containing up to 100 fields that could potentially yield null values. I figured that since they all have to be formatted once exported to the spreadsheet that this would be best place to search out and replace any Nulls.
ASKER
Well after searching some more I found this simple little snippet of code, which while it take a long time go through and clean up a 7,000 record spreadsheet, it nontheless does do the trick:
Sub BlankMe()
For Each c In ActiveSheet.UsedRange
If c.Value = "" Then c.Value = "-"
c.HorizontalAlignment = xlCenter
Next c
End Sub
Sub BlankMe()
For Each c In ActiveSheet.UsedRange
If c.Value = "" Then c.Value = "-"
c.HorizontalAlignment = xlCenter
Next c
End Sub
First, the "0.00" is a "Format"
Access can only export the raw "0", it can't force a format in Excel.
So you can import the Zero, then simply format it in Excel (simple)
Access can only export the raw "0", it can't force a format in Excel.
So you can import the Zero, then simply format it in Excel (simple)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
My objection to cekendric solution is that the asker specifically asked for a numeric value and this solution would put text in the cell.
Regards
Rob H
Regards
Rob H
Therefore a simple change to the code would be
c.value = 0
c.value = 0
I agree that the recommended comment does not answer the question as asked, which required that all values be numeric.
While I also agree with cap1 in that the best practice is to control the output with the query, and thus use Nz, if the Asker is opposed to that then I would recommend using robhenson's approach suggested in http:#a35173368
Implementing that in VBA code would look something like this:
Using the Replace method will be orders of magnitude faster than looping through the range and substituting null values with a zero, as in http:#a35713199
Patrick
While I also agree with cap1 in that the best practice is to control the output with the query, and thus use Nz, if the Asker is opposed to that then I would recommend using robhenson's approach suggested in http:#a35173368
Implementing that in VBA code would look something like this:
Sub Export()
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim r As Long
Dim c As Long
Const WbPath As String = "c:\Foo.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Table1", WbPath
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(WbPath)
Set xlWs = xlWb.Worksheets(1)
With xlWs
r = .UsedRange.Rows.Count
c = .UsedRange.Columns.Count
.Range(.Cells(2, 1), .Cells(r, c)).Replace "", 0
End With
With xlWb
.Save
.Close
End With
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
MsgBox "Done"
End Sub
Using the Replace method will be orders of magnitude faster than looping through the range and substituting null values with a zero, as in http:#a35713199
Patrick
All,
Following an 'Objection' by robhenson (at https://www.experts-exchange.com/questions/27024483/Automated-Request-for-Review-Objection-to-Accept-Q-26885726.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
At this point I am going to re-start the auto-close procedure.
Thank you,
_alias99
Community Support Moderator
Following an 'Objection' by robhenson (at https://www.experts-exchange.com/questions/27024483/Automated-Request-for-Review-Objection-to-Accept-Q-26885726.html) to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
At this point I am going to re-start the auto-close procedure.
Thank you,
_alias99
Community Support Moderator
in the query, use nz([fieldName],0) for the field that may contain null values