Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conflict in 'Allow Zero length' (access97/sr2/vba)

    In Access97 I can put data in a field and can clear data from that field, so Access97 doesn't seem to care that the data is zero-length. Why should .Update care?

    Run-time error '3315' Field can't be a zero-length string.

    <pre> With rst
    .Edit
    Dim k As Integer
    For k = 0 To rst.Fields.Count - 1
    rst.Fields(k).Value = strAr(3, k)
    Next k


    .Update <<<<<<<<<<<<<<<<< Run-time error '3315'


    End With
    </pre>


    When I explore the table definition in design mode, Allow Zero Length is set to "no" (OK, so that's why I got the Run-time error on .Update), yet many/most of the records have no data in this field anyway.

    The table was created by Importing data from a comma-delimited file.

    What's the solution?

    I don't want to modify the table definition (it's not my table). Clearly zero-length data is allowed (because Access97 allows it to exist).

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    To Access there is a difference between a Zero length string and a Null. If you set the field to Required = yes then Nulls would be disallowed as well

    HTH

    Peter

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    Thanks for the response, but that doesn't explain the strange behaviour.

    Here's Access97/VBA throwing up an error message on the contents of a field, when the field contents were epressly allowed by Access97!

    I am not debating the validation rule or the error message as such, but the inconsitency.

    How come Access97 has permitted that field to have the value that it does, and yet throws up when I try to access it by program code?

    Access97 clearly is allowing a zero-length string in that field, yet Access97/VBA prompts an error!




    A separate question is how to deal with it. VBA code isn't always running on my machine; there may not be an option to change the field definition. Consider that the application may be a utility running in, oh, say Telegraph Creek, where someone has downloaded a copy. I am not in control of changing Field definitions once an application gets beyond my desktop.


    I realise too that I may have yet to smarten up my distinction between Null and Zero length, but still and all, the essntial point is that "VBA throws up on data that Access97 allows", is it not?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    Peter is correct. Your VBA code is attempting to set the field to a zero-length string, but when you clear it manually, you are setting it to a Null. To that in VBA you have to explicitly set it to be =Null. This is definitely not a problem with VBA or Access.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    Chris,

    Just do a test before setting the value:
    <pre>With rst
    .Edit
    Dim k As Integer
    For k = 0 To rst.Fields.Count - 1
    If Len(strAr(3,k)) <> 0 Then
    rst.Fields(k).Value = strAr(3, k)
    End If
    Next k
    .Update
    End With</pre>

    Francois

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    Thanks for the code.

    Or, to include WendellB's suggestion:

    <pre>With rst
    .Edit
    Dim k As Integer
    For k = 0 To rst.Fields.Count - 1
    If Len(strAr(3,k)) <> 0 Then
    rst.Fields(k).Value = strAr(3, k)
    else
    rst.Fields(k).value = Null
    End If
    Next k
    .Update
    End With
    </pre>


  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conflict in 'Allow Zero length' (access97/sr2/vba)

    Wendell, thanks for the response.

    I understand the difference between a Null and a zero-length string. I hadn't understood that the manuall clearing (as in Access97) propogated Null.

    All is explained.

    I incorporated your suggestion in this response.

Posting Permissions

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