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

 

[BiggerBrother]

There are two ways I can think of. The first is to replace the textbox with a datepicker, and this way you can set a default date, such as today.

The other is you use:
dim DateValue as string

DateValue = IIF(len(TxtDate_Filed.Text)>0, "#" & TxtDate_Filed.Text & "#", "")

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

Hope this helps

BB

 

[StlMacMan]

I tried your suggestion and still get an error message about syntax in the INTO statement. I'll try to play around w/ the syntax. It looks right to me, but the only think I changed is the code you suggested. I tried filling in the other 17 fields and just leaving the Date-Filed empty. Still no luck--Ed

 

[ArtieChoke]

IIRC, I had the same kind of problem in Access with trying to save null date values. I ended up creating a null date value of #1/1/1990# or something like that. Then you test against this value to see if it's undefined in your code.

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

 

[StlMacMan]

Dim rs As ADODB.Recordset
Dim Con As ADODB.Connection
Dim ssql As String

Const strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\EdM.SALVARMY\My Documents\Test.mdb"

Private Sub CmdSave_Click()

BiggerBrother--I just cannot figure out the syntax error in the INSERT INTO line, but that's the error message I keep getting. What do you think? --Ed

Dim DateValue As String

DateValue = IIf(Len(TxtStartDate.Text) > 0, "#" & TxtStartDate.Text & "#", "")

Set rs = New ADODB.Recordset
Set Con = New ADODB.Connection
Con.Open strCon

Con.Execute "INSERT INTO Test (Filed, StartDate) VALUES (" & TxtFiled.Text & ", " & DateValue & ")"


Con.Close
Set Con = Nothing

 

[WindDrinker]

You might check to see if you have a value in the date field prior to trying to save it, then, if you don't have a value, you don't include it in the insert list.

dim insertStmt as string

if isnull(datefield) = false then
insertStmt = "INSERT INTO Contract (Date_Filed [and 17 other fields]) VALUES (#" & TxtDate_Filed.Text & "#, [and 17 other field values])"
else
insertStmt = "INSERT INTO Contract ( 16 other fields ) VALUES (#" & TxtDate_Filed.Text & "#, [and 16 other field values])"
end if

Con.Execute insertStmt

That way you don't have to introduce a meaningless date and a null value may actually provide some valuable information.

 

[gmmastros]

If the user doesn't enter the date on the form, then you should probably enter the value NULL in to the database.

Since the text box is blank, though, you have an empty string. So your insert statement has an epty date value appearing as ##

My suggestion would be to create your string as you currently do it. But, before executing the sql string....

insertStmt = replace(insertStmt , "##", "NULL")

empty date strings will be replaced with the word NULL, without the pound signs.

If the date is unknown, the sql string should look like...

insertStmt = "INSERT INTO Contract (Date_Filed [and 17 other fields]) VALUES (NULL, [and 17 other field values])"

 

[MrVB50au]

I can't understand what the problem can be. I created my database using VisiData that comes with VB and I don't have the same problem as you do. I can create a new record and if there are any fields that I cannot complete due to lack of info' it accepts it and saves ok.

I don't know if this applies, but, when I created my fields in Visidata, I did check the box where it says AllowZeroLength.

Maybe if you have that program and as Visidata is a VB program and can be opened in VB, maybe having a look at it's code may also help.

Hope this helped some.

Regards: Andrew.

 

[HarleyQuinn]

Unfortunately the AllowZeroLength option is not available for Date/Time fields in Access (assuming that is the datatype of the field he is trying to save into).

Harleyquinn

---------------------------------
For tsunami relief donations

 

[StlMacMan]

WindDrinker--I like your solution, but I am unsure what to do with the fact that 10 of the other 17 fields on the form are date fields that might be blank. Is there some efficient way to write this (like maybe a complex conditional of some sort) that would make it practical to use this method? I can't figure out the syntax for the insert that would only capture the date fields that were not blank. Can you help me w/ a model here?

gmmastros--I tried your solution which seems like it would handle all the instances where the date value is blank, but it did not produce the InsertStmt with Null replacing the ##. Instead I get the same error message as before: Syntax error in date in query expression '##'. --Ed

 

[ArtieChoke]

Assuming all your date fields allow nulls, the easiest way would be to use a recordset and only fill in the fields that have values. That way, the insert statement is automatically created for you.

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

 

[StlMacMan]

Do you mean that I should write some kind of conditional that tests for a valid date and then only insert those?

 

[RoyVidar]

Lot's of ways to do it, most of them presented through this thread. Here's another approach, perhaps with enough details.

[tt]dim strSql as string
dim strVal as string
strsql="insert into yourtable ("
strval=" values ("
if not isnull(txtFirstDate.text) then
strsql=strsql & "firstdate, "
strval=strval & "#" & txtFirstDate.text & "#,"
end if
if not isnull(txtSecondDate.text) then
' ...for all the 17 fields...

strsql=mid$(strsql,1, len(strsql)-1) & ")"
strval=mid$(strval,1, len(strval)-1) & ")"
Con.Execute strsql & strval[/tt]

or you could concatenate the Null into it:

[tt]strsql=strsql & "firstdate, "
if not isnull(txtFirstDate.text) then
strval=strval & "#" & txtFirstDate.text & "#,"
else
strval=strval & "Null,"
end if[/tt]

Roy-Vidar

 

[StlMacMan]

Roy-Vidar, you're a smart dude. Where do you teach? Can you tell me the purpose of this part of your code?

strsql=mid$(strsql,1, len(strsql)-1) & ")"
strval=mid$(strval,1, len(strval)-1) & ")"
Con.Execute strsql & strval

Thanks.--Ed

 

[RoyVidar]

Thanx!

Dropping off the last comma of the appended strings before adding a closing parenthesis. Then executing the concatenated query string.

Teaching end users the basics of MS Office in the south east part of Norway;-)

Roy-Vidar

 

[StlMacMan]

OK, I've rewritten my "save" code and while I don't see errors in the Debug Locals window, I do keep getting a runtime error message : Syntax error in INSERT INTO statement. Locals shows me that all the db fields are part of the strsql stmt even when the strval stmt includes nulls. Null dates are showing up as ## in the strval stmt. I interpreted your intent to be to build a sql stmt that only included non-null values. Was I wrong there, or does each If...then need an else of some sort that leaves it out?--Ed

 

[StlMacMan]

Printed to the Immediate window and discovered that the strSql statement contained an extra comma because you removed one character but had added a space after the comma. Changed it to -2 and it looks fine and removed that error message. Now the error message is my old favorite: Syntax error in date in query expression '##'. Here are the two str stmts

strSql:
INSERT INTO Contract (Corps_Institution, Program, Funder, Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Funder_or_Unit, Executed_Copy_received_from_Funder, Executed_Copy_sent_to_THQ_and_Unit, Filed)

strVal:
VALUES ('Alton, IL','Emergency Services','Federal Emergency Management Administration','50000','50',#2/22/2004#,#2/22/2005#,'New',##,##,##,##,##,##,##,##,##,##)

I only filled in 8 fields which is the way the form will normally be used. There are no errors in the date fields that I can see. I just want to save the form with the occupied data. --Ed

 

[NewGuy]

If you check "RoyVidar"'s code you see:

strsql=strsql & "firstdate, "

Note the Comma and Space!

This code:

strsql=mid$(strsql,1, len(strsql)-1) & ")"

Needs to be:

strsql=mid$(strsql,1, len(strsql)-2) & ")"


That will remove the Comma and the SPACE!

 

[StlMacMan]

Thanks New Guy. I'm happy to say I figured that out which feels so good. Any idea why I'm getting the date error message. This whole thread and 3-week frustration is about trying to save a partial form to an access db when there are empty date fields on the form. Whew. It is a killing me.--Ed

 

[NewGuy]

Am I correct that you tried:

if not isnull(txtFirstDate.text) then


Maybe change it to something like this:

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

That way if the "Not isnull" does not trap it the len>0 should.

Please try that and maybe it will help.