Results 1 to 4 of 4
  1. #1
    Michail_arkov is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    2

    UPDATE queries problem : type conversion failure

    Hello,

    I have 2 tables :
    source_table : this one is an ACCESS table containing my ID and my value
    target_table : this one is a SharePoint table import by linking under ACCESS containing my ID and my value

    My SQL/ACCESS query is :
    UPDATE source_table, target_table SET target_table.Value = Format(source_table.Value,"Percent")
    WHERE (target_table.ID = source_table.ID);

    I get the following error message in french :
    Click image for larger version. 

Name:	Capture.PNG 
Views:	9 
Size:	20.1 KB 
ID:	30918



    I try to modify the format from my source_table to adapt to the taget_table format and I try to drop in SET clause the format function, but I have an another error message in french :
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	9 
Size:	15.6 KB 
ID:	30919

    If you can ever help me it would be great! Otherwise I know that I can go across these error messages via VBA but I would prefer to stay on SQL queries.
    Thank you in advance for what you can do to help me.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the sql is not making sense to me


    UPDATE source_table, target_table

    implies you are trying to update two tables at the same time - not possible

    SET target_table.Value

    do you have a field called Value? - is so it is a very bad name because it is a reserved word

    = Format(source_table.Value,"Percent")

    the format function converts a number to text. For this to be valid, target_table.Value should be text.

    Suspect you code needs to be

    UPDATE target_table
    INNER JOIN source_table ON target_table.ID = source_table.ID
    SET target_table.Value = source_table.Value

  3. #3
    Michail_arkov is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    2
    Thank you Ajax for your answer but I have the 2 same error messages again

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you'll need to explain what you are trying to do. Update one field or all fields?

    Also
    1. is your sharepoint table linked?
    2. provide the details of the relevant fields of both tables - datatypes and example values

    And as explained before Value is a reserved word so try changing it to something more meaningful like pcValue, otherwise please provide the real names for tables and fields.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Type Conversion Failure with Access 2013
    By squeasel in forum Access
    Replies: 9
    Last Post: 07-11-2017, 10:03 PM
  2. Type Conversion Failure
    By jessgold in forum Access
    Replies: 5
    Last Post: 11-12-2012, 10:39 PM
  3. Replies: 6
    Last Post: 06-14-2012, 03:39 PM
  4. Help need: Type conversion failure
    By tami in forum Access
    Replies: 1
    Last Post: 01-18-2012, 07:18 AM
  5. Type Conversion Failure
    By fpmsi in forum Access
    Replies: 7
    Last Post: 09-22-2011, 11:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums