Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303

    Question Unanswered: Insert Command rejects null values

    I'd like to use an insert command to insert whatever data that is on the unbound form. The first three fields are required and I check those values prior to executing the code below. But some fields may be null.

    Dim strSQL as string
    .
    .
    code
    .
    .
    strSQL = "INSERT INTO tblCase " & _
    "(Case_SiteName, " & _
    "Case_PatientSequenceNumber, " & _
    "Case_PatientInitials, " & _
    "Case_DOB, " & _
    "Case_ProcedureDate, " & _
    "Case_Investigator, " & _
    "Case_Institution, " & _
    "Case_Value) " & _
    "VALUES ('" & Me.cboSiteName & "', " & _
    Me.txtPatientNumber & ", '" & _
    Me.tbxPatientInitials & "', '" & _
    Me.tbxPatientDOB & "', '" & _
    Me.tbxProcDate & "', '" & _
    Me.tbxInvestigator & "', '" & _
    Me.tbxInstitution & "', " & _
    Me.grpValue & ");"

    CurrentProject.Connection.Execute strSQL

    But, when any of the last four are unanswered, i get an error. Any, or all of the last four can be blank. Any suggestions?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  2. #2
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    try this, for the last 4:

    nz(Me.tbxProcDate) & "', '" & _
    nz(Me.tbxInvestigator) & "', '" & _
    nz(Me.tbxInstitution) & "', " & _
    nz(Me.grpValue) & ");"

    it might work, if the problem is being caused because the code is trying to append nulls to a string

  3. #3
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks for the input.

    The Nz function can be very helpful. I use it often. I especially like the optional parameter that allows the user to determine the value if the result is null.

    In this case (I tried it as you suggested - just to make sure I didn't miss something) it still gives an error.

    Text
    In the case of a missing text file, it gives the error# -2147467259.
    The field myfield cannot be a zero-length string.

    If I change the Nz function to include a value if the value is null, then the Insert works.
    e.g., Nz(Me.tbxInvestigator, " ")

    Date
    If the date field is empty, I get a type mismatch. I guess, because I'm trying to send a zero length string to a date field.

    I ended up assmbling the SQL string based on if statements.

    Thanks anyway.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

Posting Permissions

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