Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    66

    Unanswered: Clearing the value in a Date Feild

    Okay this might be an easy thing to solve but I have tried all the things I can think of, to no avail. I have a field that is of the Date data type. This field has been populated and I want to clear the date that is currently contained in it. How do i do this?

    here is the code:

    Set RS1 = CurrentDb.OpenRecordset(SQL)

    If RS1.EOF = false then RS1.Fields(2) = ?????

    What should it equal to clear the current value.

    I tried "" but it is not a valid date so i get a type mismatch
    Tried using Cdate function to convert a 0 but that blows out an error
    tried setting it to Empty and Null......

    Basically if the recordset is not empty then i want to clear the date from the third field in the recordset.....any suggestions?

    Thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Cool

    What you do is to set the field to NULL.

    NULL, in database parlance, refers to "the absence of any value at all." It's not the same as an empty-string (which would be "a string-type value containing zero bytes").
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Oct 2003
    Posts
    66
    Originally posted by sundialsvcs
    What you do is to set the field to NULL.

    NULL, in database parlance, refers to "the absence of any value at all." It's not the same as an empty-string (which would be "a string-type value containing zero bytes").
    Thanks for the reply,

    I tried assigning it to Null serveral times before posting my message but my error handling routine is executed when that line of code is read. Maybe I am overlooking something else that is causeing the error....will post back after some experimenting.....

  4. #4
    Join Date
    Oct 2003
    Posts
    66
    Okay not that anyone really cares but i figured out the problem.

    this is what corrected code looks like:


    If RS1.EOF = True Then
    RS1.Edit
    RS1.Fields(2) = Null
    RS1.Update
    End If

    the weird thing is that as long as I am not assigning Null to the field then I can omit the RS1.Edit and RS1.Update methods without getting any errors....if i set it to null it blows out errors unless i include the previously mentioned methods.....doesn't makes sense to me but hey it works now. If anyone knows the reason please post it, I am interested in the WHY.

    Thanks.

  5. #5
    Join Date
    Oct 2003
    Posts
    706

    Cool

    Originally posted by adrkoehler
    Okay not that anyone really cares but i figured out the problem.
    ...
    the weird thing is that as long as I am not assigning Null to the field then I can omit the RS1.Edit and RS1.Update methods without getting any errors....if i set it to null it blows out errors unless i include the previously mentioned methods.....doesn't makes sense to me but hey it works now. If anyone knows the reason please post it, I am interested in the WHY.

    Anytime you are changing a field's value, even to NULL, then this should require Edit. Perhaps it's switching to edit-mode automatically

    We do "care" in the sense that there really are lots of us out there who have banged our tires against the very same potholes and are glad to help others avoid them. "Go thou and do likewise." :-)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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