Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    convert nulls (Acc2k3)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: convert nulls (Acc2k3)

    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?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert nulls (Acc2k3)

    The procedure is intended to change nulls to nonnull values.

    E

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: convert nulls (Acc2k3)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: convert nulls (Acc2k3)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: convert nulls (Acc2k3)

    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.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: convert nulls (Acc2k3)

    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, [img]/forums/images/smilies/cool.gif[/img] 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •