Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    44

    Unanswered: Issue with Update Statement

    I am trying to run a Update Statement in Access 2007 and receive an error of 'type mis match'

    If i take the ( " " ) out after the = of each column to update it runs, but puts blank data in each field. If i add '" + int or double + "' it gives the 'type mis match' error. Below is my update statement. Any help will be appricated.

    Dim totalprice2 As Double
    Dim ordersizecase As Integer
    Dim amountordered2 As Integer


    vSqlup = "UPDATE tblautoorder SET amount_ordered = '" + amountordered2 + "', case_ordered = '" + ordersizecase + _
    "', total_price = '" + totalprice2 + "', time_stamp = '" + CStr(Now) + "' WHERE item_code = '" & x1(ctupdate) & "'"

    dbfor.Execute (vSqlup) OR DoCmd****nSQL vSqlup

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Only text values (the data type of the field in the table) should be surrounded by the single quotes. Date/time values should be surrounded by #, numeric values by neither.
    Paul

  3. #3
    Join Date
    Jan 2010
    Posts
    44
    I changed it to this:

    amount_ordered is integer type
    case_ordered is integer type
    total_price is double type

    vSqlup = "UPDATE tblautoorder SET amount_ordered = amountordered2, case_ordered = ordersizecase, total_price = totalprice2, time_stamp = '" + CStr(Now) + "' WHERE item_code = '" & x1(ctupdate) & "'"

    And now I have an error:
    ---------------------------
    Microsoft Office Access
    ---------------------------
    Too few parameters. Expected 3.
    ---------------------------
    OK
    ---------------------------

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You still need to concatenate the variables, just not with the single quotes:

    "UPDATE tblautoorder SET amount_ordered = " & amountordered2 & ",..."
    Paul

  5. #5
    Join Date
    Jan 2010
    Posts
    44
    It's working now. Thanks for the help pbaldy.
    Last edited by tvb2727; 02-22-10 at 06:30.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, glad we got it working.
    Paul

Posting Permissions

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