convert nulls (Acc2k3)

Topic

New Reply

I have inherited a database with quite a few null fields. I’d like to call the following procedure to convert those nulls:

Sub NoNulls(TableName As String, Optional DefaultDate As Date)
On Error GoTo nonulls_err:
‘given a tablename, remove all nulls and keep them from coming back
‘watch out for reentrancy w/the doevents in the loop
………………………………….truncated……………………………………..

I am not at all sure where and when would be the best to do so. Any advise?

TIA for sharing your expertise,

Elizabeth

Reply | Quote

Replies

What do you mean by removing nulls? Do you want to delete a field from the design of the table if it is null in all existing records? Or do you want to replace nulls with a non-null value?

Reply | Quote

The procedure is intended to change nulls to nonnull values.

E

Reply | Quote

I first have to ask why you want to convert nulls to something else. Null values are used to indicate “missing data”. If you replace them with something else, it becomes hard to tell if a value signifies “missing” or has been entered intentionally.

In the second place, what would you like to change the nulls to? It should at least depend on the field type – you can’t just enter the same value in text fields, number fields, date fields, etc.

Reply | Quote

1) the nulls are causing problems with queries. Records that should be included are not because fields like courtesy title or professsional title, examples which are lookups, where left blank. I could do a series of subqueries with different join types as a work around, but why not convert nulls in numeric fields to 0 and nulls in string fields to zero length string instead.

2) the procedure is sensitive to the different data types. I’d be happy to post the procedure if you’d like to see it.

What I’m trying to figure out is 1) when to call the procedure and 2) the syntax of the call itself.

E

Reply | Quote

Why would a courtesy or professional title affect the records returned? That sounds like a poor query design. A secondary field like that should be largely irrelevant to the results in queries unless you are using an inner join on the lookup. If that’s the case, just change the join to an outer join (all records from the main table and only matching records from the lookup table) and you’ll get a null title where none was entered.

An empty string is never a good idea because then you have to remember to test for it IN ADDITION to testing for nulls.

Reply | Quote

Why would a courtesy or professional title affect the records returned? That sounds like a poor query design. A secondary field like that should be largely irrelevant to the results in queries unless you are using an inner join on the lookup. If that’s the case, just change the join to an outer join (all records from the main table and only matching records from the lookup table) and you’ll get a null title where none was entered.

An empty string is never a good idea because then you have to remember to test for it IN ADDITION to testing for nulls.

Reply | Quote

Depending on what you want to do, this could be dangerous. For example, if you have a number field and compute the average in a Totals query, nulls are not counted. This is statistically correct, since a null represents a missing value; it is not zero but it could be anything. If you replace the nulls by 0, they will be included in the average, thereby changing its value.

Instead of changing the values in the tables themselves, you could use the Nz function in queries where a non-null value is needed. Nz(a, returns a, unless a is null, then it returns b. If you still prefer to replace the nulls in the tables themselves, you can use Nz in update queries: just update each field to Nz([FieldName], 0) or to Nz([FieldName], “”) depending on the field type.

You could replace the nulls in all tables using update queries in a one-time operation now, and after that set the Required property of all fields to Yes, so that nulls won’t be allowed any more.

Reply | Quote

Depending on what you want to do, this could be dangerous. For example, if you have a number field and compute the average in a Totals query, nulls are not counted. This is statistically correct, since a null represents a missing value; it is not zero but it could be anything. If you replace the nulls by 0, they will be included in the average, thereby changing its value.

Instead of changing the values in the tables themselves, you could use the Nz function in queries where a non-null value is needed. Nz(a, returns a, unless a is null, then it returns b. If you still prefer to replace the nulls in the tables themselves, you can use Nz in update queries: just update each field to Nz([FieldName], 0) or to Nz([FieldName], “”) depending on the field type.

You could replace the nulls in all tables using update queries in a one-time operation now, and after that set the Required property of all fields to Yes, so that nulls won’t be allowed any more.

Reply | Quote

1) the nulls are causing problems with queries. Records that should be included are not because fields like courtesy title or professsional title, examples which are lookups, where left blank. I could do a series of subqueries with different join types as a work around, but why not convert nulls in numeric fields to 0 and nulls in string fields to zero length string instead.

2) the procedure is sensitive to the different data types. I’d be happy to post the procedure if you’d like to see it.

What I’m trying to figure out is 1) when to call the procedure and 2) the syntax of the call itself.

E

Reply | Quote

I first have to ask why you want to convert nulls to something else. Null values are used to indicate “missing data”. If you replace them with something else, it becomes hard to tell if a value signifies “missing” or has been entered intentionally.

In the second place, what would you like to change the nulls to? It should at least depend on the field type – you can’t just enter the same value in text fields, number fields, date fields, etc.

Reply | Quote

The procedure is intended to change nulls to nonnull values.

E

Reply | Quote

What do you mean by removing nulls? Do you want to delete a field from the design of the table if it is null in all existing records? Or do you want to replace nulls with a non-null value?

Reply | Quote