Keep getting errors saving a null (blank) date 1

[Lhuffst]

I am using access 2010I have a sql statement to insert information into a table.
If I have dates in date fields, it works fine. If I don't fill one it, I get errors.
I've read the posts and tried what was suggested but still no success.
Can someone see what I'm doing wrong?

My default database is 2007-2016

ssql = "INSERT INTO tblPermitInfo ( " & _
        " permitno, issuedate)" & _
      " VALUES ( " & _
      " " & txtPermitNo & " , " & _
      " " & IIf(txtIssueDate = 0, Null, "#" & txtIssueDate & "#") & ")"

I've also tried:

ssql = "INSERT INTO tblPermitInfo ( " & _
" permitno, issuedate)" & _
" VALUES ( " & _
" " & txtPermitNo & " , " & _
" " & IIf(txtIssueDate = 0, & "" & Null & "", "#" & txtIssueDate & "#") & ")"

and
ssql = "INSERT INTO tblPermitInfo ( " & _
" permitno, issuedate)" & _
" VALUES ( " & _
" " & txtPermitNo & " , " & _
" " & IIf(IsNull(txtIssueDate), & "" & Null & "", "#" & txtIssueDate & "#") & ")"


Original version (written under access 2003 and worked fine):
ssql = ssql + " VALUES (" & _
....
' " " & IIf(IsNull(txtIssueDate), "Null", txtIssueDate) & " , " & _
' " " & IIf(IsNull(txtExpireDate), "Null", txtExpireDate) & " , " & _
....

thanks

 

[Andrzejek]

In your table [tt]tblPermitInfo[/tt], do you allow NULL in the [tt]issuedate[/tt] field?
From what I see you do not. So you cannot have NULL in the field where you do not allow NULL to be there. :-(

But if I am wrong (it happens....), try this:

ssql = "INSERT INTO tblPermitInfo ( " & _
        " permitno, issuedate)" & _
      " VALUES ( " & _
      " " & txtPermitNo & " , " & _
      " " & IIf([blue]Len([/blue]txtIssueDate[blue])[/blue] = 0, Null, "#" & txtIssueDate & "#") & ")"

---- Andy

There is a great need for a sarcasm font.

 

[Lhuffst]

Hi Andy,
at the table level I have

Format:  Short Date
Input Mask: 99/99/00;0;_
Required:  No

I had removed the input mask and tried it but still wasn't able to save. How do you mark a datetime as being able to accept a null?

When I make your change, it still gives me an syntax error.

The immediate window shows the sql statement as:
[COLOR=#204A87]INSERT INTO tblPermitInfo (  permitno, issuedate) VALUES (  618086 ,  )
[/color]

Thanks

 

[dhookom]

What happens if you use Andy's suggestion but add ""

ssql = "INSERT INTO tblPermitInfo ( " & _
        " permitno, issuedate)" & _
      " VALUES ( " & _
      " " & txtPermitNo & " , " & _
      " " & IIf(Len(txtIssueDate) = 0, [highlight #FCE94F]"[/highlight]Null[highlight #FCE94F]"[/highlight], "#" & txtIssueDate & "#") & ")"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Lhuffst]

Andy and Duane,

Adding the quotes to Andy's suggestion did the trick. Thanks