Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Null vs zero-string problem with append query (200

    I'm trying to add records to a table using an append query. The data I'm adding comes from a table exported out of another database; those data in turn may have originated from an Excel spreadsheet.

    I'm able to match up fields OK, but 79 of the 127 records failed to append because of validation rule failure (i.e., the records don't meet the validation rules for the destination table in the main database). I've confirmed that the "validation rule" isn't the issue, but instead it's the "allow zero length string" that's causing the problem: text fields in the destination table have "allow zero length string" = "No". Text fields in 79 of the 127 incoming records DO have zero length strings (in fact, they don't have any null values).

    Is there any way to replace the zero-length strings with nulls in the incoming data set? I realize that variant data types can be assigned "null" (at least in code), but that the string data type cannot. I've tried all kinds of approaches without any luck: creating a recordset from the table and assigning field values to variant variables, setting them equal to null, vbNullString, etc.

    Short of relaxing the "Allow Zero Length String" restriction in the main database (which could have logic implications with testing for null, etc.), how is it possible to reset selected data to null in this incoming data set?

    I should add that the zero-length string data are spread out among eleven different text fields in the incoming data set.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Null vs zero-string problem with append query (200

    Have you tried something like this in your append query:

    SomeField1: iif([somefield]="",Null,(somefield])
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Albuquerque, New Mexico, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null vs zero-string problem with append query

    Wow, how did I manage to overlook that one???? Worked perfectly!

    Thanks VERY much!!
    George

Posting Permissions

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