Reset a DAO datetime field to nothing with VB6

Question

In an old VB6 program, I have a data control associated with text controls on a form. Some of table fields are datetime fields in the JET database.

There is no default value for the datetime fields, so when first displayed on the form they show up as blank. When they are eventually set, the text string is validated as a date and the data control duly updates the record.

If the user desires to erase the date (e.g., correction of prior data entry) they simply blank the field. No error occurs on the save, but the affected datetime fields are not updated. When the record is viewed again, the last entered date is visible.

Upon examination of the DataChanged property of the text control, I can see that the date value has changed to blank (= cNullString).

Therefore, I am assuming that the eventual save of the record ignores the cNullString because it considers it invalid for a datetime field.

When my user enters blank, how can I force the datetime field back to nothing so that the next time it is viewed it will be blank again?

Try entering 0. Dates are generally stored in units since a date. In Windows whole numbers are number of days and fractions are the time of the day since (1/1/1601??). — Noodles
If I do that, I'll actually be entering a date and when the user views the record again they'll see that day 0 date. I want them to see blank. I'm also trying to avoid transformations, such as detecting day 0 and making the field blank, then restoring 0 before saving. — Will Fastie

Bob77 Bob77 · Accepted Answer · 2014-09-26T23:48:22

Be sure to set the TextBox.DataFormat to "Date" or a specific date format via the IDE. Then the underlying StdDataFormat object should have its NullValue property = "" and it becomes effective.

As the docs say:

Ignored when the Type property is set to fmtGeneral. The NullValue property is read each time a null field is fetched.

I'm not sure this can be set completely in code. When set in the IDE at design-time you get the property set up as (US English locale here):

  BeginProperty DataFormat 
     Type            =   1
     Format          =   "M/d/yyyy"
     HaveTrueFalseNull=   0
     FirstDayOfWeek  =   0
     FirstWeekOfYear =   0
     LCID            =   1033
     SubFormatType   =   3
  EndProperty

However the SubFormatType seems to be the key and this property does not seem to be exposed for alteration at runtime.

Note

Tested against the ADO Data Control and not the aged DAO version. However these binding properties transcend the ADO-DAO divide so this should be applicable.

Reset a DAO datetime field to nothing with VB6

2 Answers