Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77

    Question Unanswered: how to update a query with null value

    I have one table which has 3 columns: col1, col2, col3 which col1 is integer, and col2 is string
    If I have one form with 2 text field, txt1, txt2
    I want to update the table’s col1, col2 set the value to the given value text field. since txt1 and txt2 is input from user, if the txt1 or txt2 got null value, I have problem for updating:.
    Here is my code:

    If isnull(txt2.value) or txt2.value = “”
    Val2 = “NULL”
    Else
    Val2 = val(txt2.value)

    StrSql = “update tbl1 set (col1, col2) = ( “ & val (txt1.value) & “, “ & txt2 & “) where col3 = ‘xxx’ “

    Now i have no problem for the string null value of updating. but for col1, it's integer, if there is no input in the textbox, then myupdate have problem,
    anyone know how to solve this problem??
    Thank you very much

  2. #2
    Join Date
    Sep 2003
    Posts
    228
    Why don't you set val2 to NULL and not "NULL"?

  3. #3
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    Hi, ottomatic
    thanks for reply

    val2 is correct, have no problem
    the problem is val1, because it's integer, i don't know how to express null value in the integer.

  4. #4
    Join Date
    Sep 2003
    Posts
    228
    Quote Originally Posted by huela
    Hi, ottomatic
    thanks for reply

    val2 is correct, have no problem
    the problem is val1, because it's integer, i don't know how to express null value in the integer.
    you use the keyword null

  5. #5
    Join Date
    Sep 2003
    Posts
    228
    Sorry for the confusion... This should work for you...
    Code:
    Dim StrSql As String
    
    StrSql = "update Table1 set myInt = " & IIf(IsNull(Me.txt1), "Null", Me.txt1) & " where ID = 1"
    DoCmd.RunSQL (StrSql)
    I know it is not your exact question but you should be able to figure out what you need to do from my example above.

  6. #6
    Join Date
    Sep 2004
    Location
    Dallas, TX
    Posts
    77
    Got it, thanks a lot, ottomatic.
    appreciate.

Posting Permissions

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