Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Replacing Nulls with 0

Posted on 2011-03-14
11
Medium Priority
?
272 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:cekendricks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35130959
if you are exporting a table, use a query against the table and export the query.

in the query, use nz([fieldName],0) for the field that may contain null values
0
 

Author Comment

by:cekendricks
ID: 35131021
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.
0
 

Author Comment

by:cekendricks
ID: 35131198
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35138721
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)
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 35173368
You could do an Edit Replace on the dataset once in Excel. Select the data area and then run the Edit Replace function.

In the Find box - leave Blank
In the Replace With - type 0

Expand options if not already expanded and select the Match whole cells option.

This will be doing the same as the code you found but using an inbuilt function may do it quicker.

Cheers
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 35713193
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
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 35713199
Therefore a simple change to the code would be

c.value = 0
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35713616
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:

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

Open in new window


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
0
 

Expert Comment

by:_alias99
ID: 35744749
All,
 
Following an 'Objection' by robhenson (at http://www.experts-exchange.com/Q_27024483.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
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question