Empty Date Field Problem

[StlMacMan]

I have several text boxes in a VB6 entry form that take short dates and save them to an Access 2002 db. If I fill them all it works fine. If I leave any one of them empty I get the following error message: Syntax error in date in query expression '##'.

The code used to Enter and Save them is:

Con.Execute "INSERT INTO Contract (Date_Filed [and 17 other fields]) VALUES (#" & TxtDate_Filed.Text & "#, [and 17 other field values])"

As I say works fine if they are all filled, hangs if any one is empty. I have read about a "Null" problem but don't know if this is an instance of that since I don't get the "Invalid Use of NULL (94)" error message. Any help appreciated. --Ed

 

[StlMacMan]

You are correct. I did try
if not isnull(txtFirstDate.text) then

I tried your suggestion--had to add an extra set of () before Len and after 0--got exactly the same response. It lists all the fields and all the values showing the blank dates as '##'. Any idea why it is not omitting the nulls since it seems like the code tells it only pick up what is "not null"? --Ed

 

[ArtieChoke]

From help:

"The Null value indicates that the variable contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string."

I doubt that the text box, which is a string, would return a null value. You should get a zero-length string out of it, so the len function = 0 should suffice. In your if statement

if (not isnull(txtFirstDate.text)) or Len(txtFirstDate.text)>0 then

the isnull will return false in all cases, so the len test doesn't matter, since the first part of the test will be true. That's why you still get the ##s in the insert statement.


"I think we're all Bozos on this bus!" - Firesign Theatre

 

[StlMacMan]

Thanks to all who helped. W/ Roy-Vidar's basic schema and ArteChoke's Len comments by removing the first half of the test and only using the Len component, it now builds a sql string w/ only the non-blank fields. Unfortunately because I have default text in some of the fields, if the user hits save w/o entering any data, it does not save to the db w/ the default text. I'll look into that next. Thanks again.--Ed