Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    3

    Unanswered: Access VBA and sql-statement problem

    H!

    I have problem with decimals in access when I use VBA code and sql-statements.

    Example:

    CertCmd.CommandText = "UPDATE certificate " & _
    "SET plastic = """ & plastic_work & """, " & _
    " lenght = " & lengt_work & " " & _
    "WHERE Cid = """ & Cid_work & """ "

    Lenght and lengt_work are numeric and have decimals. I use comma as decimal separator.

    I ger error mismatch number of variables, because decimal separator is recognized as parameter separator.

    How can I solve this problem? I cann't change decimal separator in my system.

    Regards Pena

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are updating an SQL database with numeric values then you don't need to use the " to delimit fiels.

    Equally your, if properly configured with be localised (setup) to accept values as per internationalisation settings

    if your db is configured to use a decimal as a separator then you have a choice - either set the db to use commas as decimal seperators or post values using a format

    CertCmd.CommandText = "UPDATE certificate " & _
    "SET plastic = """ & plastic_work & """, " & _
    " lenght = " & format(lengt_work,"#0.00") & " " & _
    "WHERE Cid = """ & Cid_work & """ "

    assumign that Cid is a string / text varaibale that shoudl work - Usually its not a good idea to call text variables xxxID - but its your db....

    to debug the sql try building the SQL vefore assigning it to CerTCommand
    eg
    strSQL="UPDATE certificate " & _
    "SET plastic = """ & plastic_work & """, " & _
    " lenght = " & lengt_work & " " & _
    "WHERE Cid = """ & Cid_work & """ "
    msgbox strSQl
    CertCmd.CommandText = strSQL

    that way round you have a chance to see what you are actually trying to send the SQL engine as opposed to what think you are sending.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2004
    Posts
    3

    Angry Format in sql

    Hi!
    I tried format as adviced. Of course it must be written #0,00 because I use comma (,) as decimal separator.
    When using format Access doesn't give error message any more, but the walue of the field is rounded. If the value of lenght_work is 10,98 the field Lengt will be 11. Why?

    Regards Pena

  4. #4
    Join Date
    Dec 2004
    Posts
    3

    Thumbs up Solution

    When I changed the ordinary statement:

    CertCmd.CommandText = "UPDATE certificate " & _
    "SET plastic = """ & plastic_work & """, " & _
    " lenght = '" & lengt_work & "' " & _
    "WHERE Cid = """ & Cid_work & """ "

    Numeric value with decimals is surrounded by '" ..... "'

    Now it works!

    Do you agree?

    Regards Pena

Posting Permissions

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