Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Unanswered: Null value in Sql INSERT statement

    I have the following insert statement that takes values that are on a form and inserts them into another table. However, it only works if there is something in the text box. If I have an empty text box it won't work. How do I account for the NULL value?

    strSql1 = "INSERT INTO tblArchivedDates(clientID, DateReferal, ActiveStart, DischargeDate, DischargeCode, Username) VALUES (" & Me!ClientID & ", " & Format$(Me![Date Referal], "\#mm\/dd\/yyyy\#") & ", " & Format$(Me![Active Start Date], "\#mm\/dd\/yyyy\#") & "," & Format$(Me![Discharge Date], "\#mm\/dd\/yyyy\#") & "," & Me![Discharge Code] & ", '" & UserName & "');"

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Use the Nz function:
    Code:
    Format$(Nz(Me![Date Referal], ""), "\#mm\/dd\/yyyy\#")
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Thanks!

    But I'm still getting an error message that something is wrong with the syntax.
    Is it possible since my table (that the insert is going to) has the field set as date that it won't insert a null value into a date field?
    If so, how do I get around that?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What exactly is the message?
    2. If you open the table in design view, you can select the field and specify whether it accepts Null values or not (see attachment)
    Attached Thumbnails Attached Thumbnails NullAllowed.jpg  
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Message is:
    Run-time error '3134'
    Syntax error in INSERT INTO statement


    My table is set to accept null values but I'm still getting the error message.
    Here's my insert statement:

    "INSERT INTO tblArchivedDates(clientID, DateReferal, ActiveStart, DischargeDate, DischargeCode, Username) VALUES (" & Me!ClientID & ", " & Format$(Nz(Me![Date Referal], ""), "\#mm\/dd\/yyyy\#") & ", " & Format$(Nz(Me![Active Start Date], ""), "\#mm\/dd\/yyyy\#") & "," & Format$(Nz(Me![Discharge Date], ""), "\#mm\/dd\/yyyy\#") & "," & (Nz(Me![Discharge Code], "")) & ", '" & UserName & "');"

    The statement does work when there are values in the fields and I only get the error when there is a null value in one (or more) or the fields.
    Any ideas?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I answered too quickly, sorry. Try:
    Code:
    IIf(IsNull(Me![Date Referal]), "Null", Format$(Me![Date Referal], "\#mm\/dd\/yyyy\#"))
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Much better. Thank you!

    One last glitch. On the last value I'm getting "argument not optional"

    " & IIf(IsNull(Me![Discharge Code]), "Null") & "

    This value is just a number, not a date like the others. Not sure what I'm missing.

  8. #8
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    Whoops! Silly error on my part. I saw what I was missing. Thanks for your help!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •