Changing blank fields to zero

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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

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.

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.

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.

Thanks all. Great help, as usual

You are welcome!

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.

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.

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.

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

It was attached I swear it was!!

Maybe not
CaptureA.PNG

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.

I thought it should do that too.

Is this question closed or not ?

I thought I had closed it.

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