Convert 0 (zero) to Null

[luigen]

Hi.
I have a db with a numeric field called Eur_2.
This field is populated with data imported from an excel file.
When the Eur_2 excel field contain Null, in the correspondent Access Field the value inserted is 0 (zero).
I have necessity to put in the Access field Null value.
The VBA code that I use for import is:

Eur_2 = XL.Application.Cells(indriga, indcolonna + 10)
If Eur_2 = 0 Or _
Eur_2 = Empty Then
Eur_2 = Null
End If

but when i open the Access table, the value inserted is 0 (zero).
There's something to do for change 0 (zero) to Null?

Thanks
Luigi

 

[KeithG]

Look at the default value for the field in the table. I bet it is set to Zero. If you want null values delete the default value property.

 

[boblarson]

Make sure that the field in the table doesn't have a default value of 0 set (that is the default that occurs when you create a new numeric field in a table and if you don't remove it, it will put a zero in place of a null).

Also, change your code so that it populates the field if there is a value and only if there is a value. Don't try to set it to null.

In other words:

Dim v As Variant

v = Nz(XL.Application.Cells(indriga, indcolonna + 10), 0)
   If v <> 0 And v <> "" Then
     Eur_2 = v
   End If

 

[The_Doc_Man]

I will not attempt to tell you what you must do, but please be aware that there is a school of thought that says you should never use null to mean anything. Have another indicator separate from the numeric field.

Null as a value tends to break a lot of functions. You end up having to program your way out of so many messes that it is almost painful. If you have a separate indicator that says "This record isn't ready" or whatever the null represents, you will have fewer accidents.

 

[KeithG]

The_Doc_Man is correct (he usually is), if you use calculations on this field I would leave the default value as zero or you will be using the NZ() function quite frenquelty in your queries and reports. Also understand that a Null value is an unknown value and a 0 represents an actual value. So my question to you is, is the fields value unknown at this time? If so I would use a Null value but if not I would have a default value of 0.

 

[The_Doc_Man]

Thanks for the vote of confidence, KeithG.

 

[RoyVidar]

> there is a school of thought that says you should never use null to mean anything.

I am unfamiliar with that school of thought.

I am, however, aware there is a school of thought that says you shall not record missing information in a database, that such violates the purity of the relational model (Date/Darwen?).

Note that this approach means that you do not record missing information in a database at all. There will be no Null fields, no text fields being ZLS, no fields containing magic numbers, magic dates or dummy values. Missing information is simply not recorded in the database. Here are some thoughts on how http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf I think this is pretty interesting, but perhaps a wee bit academic and unpractical?

And another school saying that Null is, and has always been an integral part of the relational model (which is attributed to Codd in his later works).

If one accept the concept of recording missing information, which is what I think Codd is speaking about, then the next step is choosing which method to use. Each method has their own advantages and drawbacks. Null is designed explicitly for the purpose of recording missing information, and is reasonably consistently implemented for most field types across database platforms. I will use that over more proprietary or "home grown" methods.

> Null as a value tends to break a lot of functions.

I don't think it is fair to say that. If a program barfs because a function is passed Null, and this function cannot handle it, I don't think it is fair to blame neither Null nor the function. The one to blame, is the developer, who has not done their job

 

[The_Doc_Man]

The one to blame, is the developer, who has not done their job

Exactly my point, Roy. Design it so it won't break. Which is why using NULL is not a part of good design for Access.

However, the school of thought that says don't store a record until you have a complete record doesn't cover all bases very well, particularly if there is such a thing as, say, a patient calls an HMO to get authorization for a charge. So they give him a number but cannot complete the transaction until the doctor calls back with a proper diagnosis code. If you don't store that authorization record NOW, you stand a chance to lose the number. So go ahead and store it, but with a "state" variable that says "Authorized only" or something like that. And you fill in later when you can put a diagnosis code in the record. And perhaps a prognosis code, depending on what is being tracked.

Where the data being tracked can be ephemeral, I do not stand with the crowd that says never store an incomplete record. (Part of my pragmatic approach, of course.) I don't use dummy values. I use state codes to define the stages of my data record if there is an increment update as part of my business model. I agree with the "don't store it until complete" school when my business model says I need complete data for a record to be "official." Again, pragmatic and business-model driven.

When the question is, "How do I force something to be a null rather than ZLS or 0 as appropriate to the data type?", you are now dealing with using NULL as the 257th value of a byte or the immoral equivalent for a string. The problem is that if you try to migrate a database such as this to another platform, you always have to worry about how nulls are treated. Design your way out of this dilemma before you get there and you never need to worry no matter WHAT platform you are on.

Your business model needs a positive indicator for something being ready or not ready. It now comes down to viewpoint, but to me, testing for a NULL is not testing for something but testing for the absence of something. Sure, you can do it. Piece of cake. Except conceptually you had better not have slipped up even once in the assignment of nulls because of their propagation when used in any expression or function. That NULL becomes like a cockroach that get behind walls where you never expected it to be.

I guess my issue isn't so much that you have a null, but that in Access a null has so many negative and insidious side effects to be completely trustworthy as an indicator of something.

 

[DJL910]

Make sure that the field in the table doesn't have a default value of 0 set (that is the default that occurs when you create a new numeric field in a table and if you don't remove it, it will put a zero in place of a null).

Also, change your code so that it populates the field if there is a value and only if there is a value. Don't try to set it to null.

In other words:

Dim v As Variant

v = Nz(XL.Application.Cells(indriga, indcolonna + 10), 0)
   If v <> 0 And v <> "" Then
     Eur_2 = v
   End If

This was a very helpful approach... If NULL, don't touch the field, otherwise populate the field

 

[Gasman]

Glad to know a 17 year olf thread is still useful.