×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Keep getting errors saving a null (blank) date

Keep getting errors saving a null (blank) date

Keep getting errors saving a null (blank) date

(OP)
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


CODE -->

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

RE: Keep getting errors saving a null (blank) date

In your table tblPermitInfo, do you allow NULL in the issuedate 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. sad

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

CODE

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

---- Andy

There is a great need for a sarcasm font.

RE: Keep getting errors saving a null (blank) date

(OP)
Hi Andy,
at the table level I have

CODE -->

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.

CODE

The immediate window shows the sql statement as:
INSERT INTO tblPermitInfo (  permitno, issuedate) VALUES (  618086 ,  )
 

Thanks

RE: Keep getting errors saving a null (blank) date

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

CODE --> vba

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

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

RE: Keep getting errors saving a null (blank) date

(OP)
Andy and Duane,

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close