Null Value

[Little_Man22]

I'm having trouble passing a couple of values because I think that they have zero-length strings. How can I change it so that if the value of the field of income = " " then set this value = to Null?

Ryan.

 

[R. Hicks]

Nz([YourFieldName],0)

HTH
RDH

[This message has been edited by R. Hicks (edited 11-03-2001).]

 

[Little_Man22]

Where can I put this bit of code though? The problem is that origionally when the database was created the income and net worth fields had default values of 0. But our staff has gone through and deleted the majority of these zeros for our clients given that they didn't have incomes of '0' but rather their incomes simply weren't known. So I need this bit of code to be written into procedure x so that Access recognizes these fields as being null as opposed to zero-length strings. Which procedure x should I use though?

Ryan.

 

[R. Hicks]

Why don't you build a temporary Update Query to convert all the Null values in the table to 0's?

After the update .... delete the Update Query.

RDH

[This message has been edited by R. Hicks (edited 11-03-2001).]

 

[Little_Man22]

I don't want '0''s in the fields though...that was the problem to begin with. It's not accurate to have the clients income listed as 0. I need some kind of code in the form procedures that will convert the current value or income/net worth to Null if it is a zero-length string.

Ryan.

 

[R. Hicks]

Then try this in a query as an Expression:

ExpName:IIf([YourFieldName] = "", Null, [YourFieldName])

RDH

 

[Pat Hartman]

Are these fields a numeric data type? I think zero-length strings occur only in text fields. Exactly what trouble are you having?

 

[Little_Man22]

Pat: see post where jwindon and I were trying to figure out code...it's from 2 days ago in this forum.

 

[Rich]

Isn't it much easier to do as Ricky suggests and set the value to 0, may not be accurate but you can easily hide the zero entries to give the impression of a null entry. Sometimes it's easier to cheat.

 

[R. Hicks]

If the Datatype is Numeric ..... then it will have to be Null or 0. Like Pat has replied, a numeric datatype can not be a zero length string ("").

RDH

[This message has been edited by R. Hicks (edited 11-04-2001).]

 

[Little_Man22]

OK Rich I see what you are saying.

How do you hide the data if it is equal to 0 though? Also, how can I set all of the 'empty' strings to 0?

Ryan.