Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why can't zero-length strings be inserted using IN

    Why can't zero-length strings be inserted using INSERT INTO SQL?

    When I try to insert fields of a new record, and if one of them is a zero-length, the insert fails.

    I can insert nulls but not zero-length- very odd! Following is my SQL:

    strSQL = "INSERT INTO tblNTLogIgnoredEntries (Server, LogName, Detail02) "
    strSQL = strSQL & "VALUES ('" & rsTrimAdd("Server") & "', '" & rsTrimAdd("LogName") & "', "
    strSQL = strSQL & "'" & rsTrimAdd("Detail02") & "'"
    strSQL = strSQL & ");"

    If any of the values is zero-length, the insert will fail. If all other cases (non-zero and null), insert is successful;

    What should I do to have success? My workaround is to populate the source table with a single space character for each zero-length field.

    Thank you!

    --llyal

  2. #2
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why can't zero-length strings be inserted using IN

    My understanding is that the entire SQL string is checked for syntax/compatibility with the design of the table. When I have had problems with this, I have gone to the design of the table I am trying to add records to and ensured that the field is marked as Required=No.

    That works, but whether it is the correct thing to do or not, I have no idea [img]/w3timages/icons/smile.gif[/img].

    HTH.

    Kiwi44

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

    Re: Why can't zero-length strings be inserted using IN

    A zero-length string and a null are two very different creatures. A null is the absence of a value. You aren't really populating the field at all. However, a zero-length string IS a value, although you'll have a hard time spotting it in a table or query.

    There isn't any good reason to append a zero-length string that I can think of. Text fields and number fields should both accept Nulls. In this case, have you examined why you would have zero-length strings in any of these values in the first place?
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why can't zero-length strings be inserted using IN

    I converted Null values to zero-length strings because i was having problems with queries when doing text searches (example, queries with LIKE '%info%'). I think the queries would return nothing if i search several fields of a record looking for a string match and one of the fields contained a null.

    So this is why I replaced nulls with zero-length string. Now I want to filter this table and make a new table of the filtered data. Well, i cannot insert zero-length strings!

    The workaround i am using is- to replace the zero-length with a single-space character. I get the results, but i don't understand why Access cannot handle zero-length strings in INSERT statements.

    Thanks!

    --llyal

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

    Re: Why can't zero-length strings be inserted using IN

    It CAN handle them, but only if the field is set to allow them. Access is simply doing what you tell it to do. If you tell it to do something illegal (because the AllowZeroLength property is set to false), you can't expect it to accommodate you. You need to set the constraint in the Create expression.

    As far as the query goes, it will only return records with "info" in the field. It will ignore records with null in the field. If you want to see records with nulls as well, you have to change the criteria expression to include Or Is Null.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why can't zero-length strings be inserted using IN

    Got it, and thanks!

    --llyal

  7. #7
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why can't zero-length strings be inserted using IN

    I think I understand the difference (now!) between zero-length strings and nulls. My question now relates to parsing a tagged text file into a database.

    I dim the variables as strings, then loop through each record in the file, assigning the text headed by a tag to one of the variables. How do I ensure that the contents of the variable is not carried over to the next record (with nothing to be assigned to the variable). Can I use 'set myVar = Nothing', or is there something else?

    Kiwi44

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

    Re: Why can't zero-length strings be inserted using IN

    Not unless MyVar is an object! You only use the Set keyword with an object variable. Ditto for Nothing. What you want is MyVar = Null, assuming that MyVar is a variant. Otherwise, initialize it as an empty string.
    Charlotte

Posting Permissions

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