Changing blank fields to zero

Derek Brown
Derek Brown used Ask the Experts™
on
Is there a single command that will change blank fields to zero in a spread sheet?

50 columns 50 rows but 20 number columns some with random blank fields. I am importing to MS access. It doesn't like blank fields
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Here is one approach I have used in the past, it's not a single "command" but it isn't too hard to use.

https://www.extendoffice.com/documents/excel/772-excel-fill-blank-cells-with-0-or-specific-value.html#a1


»bp
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Or select the area(s) that contains the blanks and then run this little macro.

Sub ChangeBlanksToZeros()
Dim cel As Range

For Each cel In Selection
    If IsEmpty(cel) Then
        cel.Value = "0"
    End If
Next
End Sub

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
There is always the possibility of using automation to connect to Excel and import everything cell by cell...this will give you complete control over to what to import it and what it would be presented to access :https://social.msdn.microsoft.com/Forums/en-US/922ff730-7124-413a-a86f-465db226c1d7/export-data-to-excel-in-a-specific-format?forum=accessdev
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Don't import the worksheet but link it.

Then create a simple append query using the linked table as source and, for the offending field(s), replace the Null values with zeroes:

TrueNumber: Nz([LinkedField], 0

Open in new window

)and pass this (or these) to your table in Access.

Author

Commented:
Hi Gustav

Am I correct in assuming you mean update query and would I be correct in thinking that this has to be done with individual queries one for each column or field
Distinguished Expert 2017

Commented:
The "blanks" may be null or ZLS (Zero Length String).  Nulls can be inserted in numeric fields as long as the fields are not defined as required.  ZLS are STRINGS and so cannot be inserted into numeric fields

TrueNumber: IIf(LinkedField & "" = "", 0, LinkedField)

Concatenating  the Linked field and then checking is for a ZLS which is replaced with 0 will take care of both nulls and ZLS.

Enclosing a 0 in quotes makes it a string.

Somefield = 0 ----- numeric value
Somffield = "0" ------ string value
Somefield = ""  ---- ZLS - works ONLY with text fields and is generally undesirable.
Somefield = null  ---- null - works with all data types as long as they are not defined as required - always better than ZLS for text fields.

For example, if you have a LastName field and you want it to be always present so you set it as required, if you leave the AllowZeroLengthString property to stay as yes, you defeat the purpose since someone can enter "" and not actually provide a name and your app won't complain.  

And finally, my advice on zero vs null.  Zero has a meaning.  it is a valid numeric value and Access will treat it as such.  The assumption is that a value is present and it is KNOWN to be zero.  Whereas if the default is 0, then you can never tell if the value is known or assumed.  A real world example is a grade table.  If you want to produce an average, Access ignores null entries so if the test record has already been added to the table but the grade is null, that grade will be ignored when calculating the average but if you set the default to 0, the test will be averaged as if the grade is known to be 0.  

So - the average of (3, 0, 3) is 2
But - the average of (3, null, 3) is 3

Given this info, you may want to alter your table definition and append nulls rather than zeros.
Distinguished Expert 2017

Commented:
Derek to answer your question to Gus.  NO.  The data from the spreadsheet is added to the table using an append query and you would have an expression for each column that you need to manipulate.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Am I correct in assuming you mean update query and would I be correct in thinking that this has to be done with individual queries one for each column or field?

No. That's why I wrote Append Query. The source is your linked table, the target your current table in Access. One query.

Author

Commented:
Thanks Gustav, now I get it.

Thanks Pat, The zeros are needed to stop miscalculations in other code and queries with calculated fields referencing these null fields. Interesting post.

Author

Commented:
Thanks all. Great help, as usual
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
Distinguished Expert 2017

Commented:
The zeros are needed to stop miscalculations in other code
Only if having a meaningless zero value is more important than properly handling nulls in calculations.

If you want a value to be required, make the field required at the table level (once you fix all the existing nulls) and you can set the default to 0 if you don't need the user to actually enter anything.

Author

Commented:
Hi Pat

I hope I understand you correctly.

There are 780 fields required in specifying a fire door set (Set: door with frame and ironmonger) 600 of them number fields. When we fill in a form to complete a specification I simply set the default value in the underlying table to Zero and the after update field on the form to Nz(field,0) So it has never been an issue. Until that is 11 years later for the first time we start to import from Excel. You are correct I could look at the many hundreds of calculations and queries and change all to nz filelds. Just seems easier to fix the import rather than the calcs.
Distinguished Expert 2017

Commented:
For this particular application, it probably won't cause a problem since I doubt you will be averaging any of the attributes :)  But, simply making the fields required in the table definition and using 0 as the default eliminates any need for form level code.  It would also have fixed up your Excel import because nulls would be converted to 0 as the record is appended.

Author

Commented:
Hi Pat

I have just been playing around with a simple append query to check that appending numeric fields will automatically insert a Zero when default value in the numeric field of the target table is set to zero. I get Null Vales. I also have A and B which has the Nz function in query. Here is the SQL:

INSERT INTO Table1 ( DH, ArchitravesWidth, ArchitravesThickness, ArchitravesWidthBack, ArchitravesThicknessBack )
SELECT nz([Door Height],0) AS Door, tblImportData.[Architraves Width], tblImportData.[Architraves Thickness], Nz([Architraves Width Back],0) AS A, Nz([Architraves Thickness Back],0) AS B
FROM tblImportData;

The result is attached

Author

Commented:
It was attached I swear it was!!

Maybe not
CaptureA.PNG
Distinguished Expert 2017

Commented:
Sorry about that, I thought that the append query would also populate using the defaults.  I guess it is just forms that append the default for you although I swear Access used to do this.  I tried it and if the fields are defined as required, the append query just discards the rows rather than filling them with the defaults and if you make the fields not required, it appends the rows but doesn't populate with the defaults.

Author

Commented:
I thought it should do that too.
John TsioumprisSoftware & Systems Engineer

Commented:
Is this question closed or not ?

Author

Commented:
I thought I had closed it.
John TsioumprisSoftware & Systems Engineer

Commented:
I see some talk going on...ok then....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial