Re: Representing Empty DATE values as NULL

Hello Avery,
"Avery Payne" <apayne(at)pcfruit(dot)com> wrote:

AP> "Empty Date" is a Visual FoxPro concept.
AP> |...snip...|

I see.

AP> Note that you can also see "empty" dates in Access. Create a new
AP> table with an autonumber field named "MyID", a date field named
AP> "MyDate", and a text field named "MyText". Open the table after you
AP> have saved its definition; then proceed to fill in several rows of
AP> data using just the "MyText" field alone. Each time you do this,
AP> the MyID field will auto-increment and a new number appears; and
AP> your text is saved as you move off of each row; but the "MyDate"
AP> field remains -->>EMPTY<<--, which is not the same as NULL. This
AP> is a common theme in Microsoft's smaller database products, where
AP> the "databases" function more like storage containers than actual
AP> database engines. Use this with Visual Basic and you have an
AP> environment where MS programmers are most likely to take the raw
AP> value of a field (typically an empty string, "") and pass it
AP> directly into the table that is mapped to the form's field.

In Access, the Empty constant and IsEmpty() function are pure VB/front-end
concepts. They can only be used in front-end stuff, i.e. modules, forms,
reports, and macros. You cannot mention them in SQL statements, especially
passed to Jet via ODBC.

OT1H, when using so-called "bound forms", the empty fields is automatically
converted to/from NULL. Within VB code, the Value property of any bound
control equals to NULL (unless it is defaulted). OTOH, for an unbound
control that knows nothing about database, the Value property is Empty
initially.

AP> To prevent PostgreSQL from choking on this, set "Server Side
AP> Prepare" to ON and select option 0x8 when using Hiroshi's
AP> experimental driver, and the empty date fields will be automagically
AP> translated to a sane value, NULL, instead of just " / / ".

Thanks to driver developers for making the option look encrypted, so
"conformant" ODBC clients need not bother about that :-).

AP> Hope this has answered your questions...

Yes, thanks.
--
Sincerely,
Dmitriy Ivanov
Common Lisp ODBC interface - lisp.ystok.ru/ysql.html