DAO 2.5 won't convert zero length string to null in date field 
Author Message
 DAO 2.5 won't convert zero length string to null in date field

We have thousands of lines of code relying on DAO converting zero
length strings "" to NULL on the fly, but it doesn't work in VB5:

' generates a type conversion error when txtDate = ""
rsCases("dateReceived") = txtDate

This worked with DAO 2.0 in VB 3.0 and 4.0, but not with DAO 2.5
compatibility with VB 5.

Is there a quick way out? We don't have time to waste in looking for
all such assignments and wrapping them in a value checker :-(
I know we can check for zero length like this,

rsCases("dateRecd") = iif(len(txtDate) = 0, NULL, cvdate(txtDate) )

or in similar ways, like writing a function DateOrNull(sStringDate),
but that means trying to find all the lines where this is possible and
changing them!!!

We have found a solution in this newsgroup -- THANKS to Frank
Bainbridge and Dejanews --  for a similar problem storing zero length
strings in text and memo fields: create the V1XNullBehavior
boolean-type database  property and set it to True to allow the
conversion of zero length string to null on the fly even though the
'allow zero length' property of a text or memo field is set to 'No'.
But i can't find a similar hack for the date field....


(How stupid in the first place of the db engine to have allowed such
an implicit conversion in the past! Assigning a "" to a date field????
STOOOPID! Now we're stuck..............)

Sat, 13 Jan 2001 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. DAO 2.5 won't convert zero length string to null in date field

2. Zero-Length Strings vs. NULL, Access Date Fields

3. Convert Zero-Length String to Null

4. VB4.0 Field can't be a zero-length string

5. Nulls / Zero Length Strings -Newbie Question

6. Newbie Question - Nulls vs zero length strings ???

7. Zero-length / null strings

8. Bound VB4 TextBoxs, Nulls and Zero-Length Strings

9. Zero-length / null strings

10. Null and Zero Length Fields

11. Allow Zero Length String in a field using SQL

12. SQL created Field can't be zero-length


Powered by phpBB® Forum Software