Clear Date Field with ADO

[PaulSamuelson]

I have a routine I use to change data in an ADO recordset (Access File). It works for all types I have tried, except it does not allow me to clear a date.

It will change a date field, but not clear one. How do I clear a date field?

If FieldValue==""
  RS1.Collect(dbField) = ObjectType("NULL",0)
Else
  LastError() ;reset to 0
  ErrorMode(@off)
  RS1.Collect(dbField) = FieldValue
  ErrorMode(@cancel)
  Error = LastError()
  If Error!=0
    Message("Oops","Record [":iGridSubjects.RowKey(editRow):"] Field [":dbField:"] not updated (probably the wrong data type).")
  End If
End If
RS1.Update()


Thanks,

Paul

[td]

I deleted my original post because it was off the mark but do you get an error message when you attempt to assign the NULL variant to the field or when you call Update?  Even if you don't get an error, I suppose it could be a restraint set on a particular database to prevent NULL values.

[stanl]

Might not be relevant but if your date field is required in your table structure nothing will work.

[td]

I have a very vague notion that Access will set a NULL date field to some default date. Don't know if this is configurable or if it is even a feature.   May simply be a false memory.

[PaulSamuelson]

I deleted my original post because it was off the mark but do you get an error message when you attempt to assign the NULL variant to the field or when you call Update?  Even if you don't get an error, I suppose it could be a restraint set on a particular database to prevent NULL values.

I removed the Error stuff, which is no longer needed, because I know exactly which data types are affected (that code was originally used on a grid, where I didn't necessarily know the type). The following code now DOES work. It will update (including remove) date or text types.

Apparently, the problem had to do with the order I cleared the fields. If I cleared the text field first, the record was no longer in the recordset when it came back to clear the date. If I cleared the date first, everything works as it should.

Thanks,

Paul

If editVal == ""
  RS1.Collect(dbField) = ObjectType("NULL",0)
Else
  RS1.Collect(dbField) = editVal
End If
RS1.Update()

[td]

Access does allow a table to be configured with required fields.  This could cause a problem similar to the one you experienced. Obviously, it wasn't an issue here. 

Thanks for reporting your solution.  It may be helpful to others in the future.