Results 1 to 6 of 6
  1. #1
    ljsincla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6

    Need help with syntax for Update Query syntax error for the UPdate To: field

    Need a bit of help.


    Each day I work a PO Agenda spreadsheet. I would like to takethe comments from the spreadsheet I work today and move it to the spreadsheet Iwill work the next day. I created a selectquery to accomplish this task. However,when I change my select query to an update query I am having a problem updatingthe new PO Agenda. I am having troublewith the syntax in the Update to: field of my query.

    Table Agenda is my new table.

    PartNumber DocumentNumber Comments

    Table OldAgenda is my old table with comments
    PartNumber DocumentNumber Comments
    111222333 FB600345005021 ContactedBuyer 9/8/2018
    444555666 FZ607801248009
    777888999 FE900165017280 RefurbishedValves on VSR

    The Query works fine with the syntax below. There is a relationship between each field inboth tables.
    SELECT [Old Agenda].HOME_ADDRESS
    FROM [Old Agenda]
    WHERE ((([Old Agenda].Comments) Is Not Null));

    FIELD: Comments
    Table: Old Agenda
    Criteria: Is Not Null

    PartNumber
    DocumentNumber Comments
    111222333
    FB600345005021
    ContactedBuyer 9/8/2018
    777888999
    FE900165017280
    RefurbishedValves on VSR
    Now when I change the Query to an Update Query the data doesnot transfer from “Old Agenda” table to the “Agenda” table when I use thefollowing info.
    Field: Comments
    Table: Old Agenda
    Update: [Agenda]
    Criteria: Is Not Null
    or
    Field: Comments
    Table: Old Agenda
    Update: [Agenda].[Comments]
    Criteria: Is Not Null

    Any help to get the correct Syntax for the Update: field will be appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You say 'spreadsheet' but do you mean table?

    Why are you 'moving' data? Why not just use queries?

    If you want to 'update' existing records with data from another table then query needs to join tables. If you want to 'insert' new records into a table with data from another table then you need an INSERT SELECT syntax.

    It is just not clear what you are trying to accomplish.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ljsincla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    Thanks for the response. My reference to spreadsheet is definately not clear. This is what I wanted to say. Today we use Excel spreadsheets on a day to day basis. We update the spreadsheet today and tomorrow we will move the comments from todays spreadheet to tomorrows (New spreadsheet)spreadsheet. I intend to import the spreadsheet into Access into a Table called Agenda. My intention is to use Access queries to move the data from the Old table (OldAgenda) to the new table Agenda. I built the tables as identified above and used queries to select the data. The query has selected the correct data. However, when I convert the query to an Update query I get a syntax error "Enter Parameter Value ?" on my Update Field.
    Any suggestions on the correct syntax for the Update field. I used two inputs
    [Agenda] and
    [Agenda].[Comments] with the same result
    "Enter Parameter Value ?"

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I really don't understand why you have two tables. Conventional approach in relational database would be one table and apply filter criteria to select records, perhaps the agenda date.

    Why even use Excel? Input data directly in Access.

    Again, perhaps UPDATE is not the correct action. If you want to copy records from one table to another table, that is an INSERT SELECT action.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ljsincla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2018
    Posts
    6
    I agree with you. We currently use excel because the PO Agenda is provided to my department in a Excel spreadsheet. My manager wants to keep it the same. However, I believe Access is a better tool to manipulate the data. Therefore I have started the process of converting the PO Agenda received daily into Access to do all the work. My intention is to import the file into Access and work in Access. Then export it to a Excel spreadsheet and send it back to my boss without a change to my boss or customers who utilize the PO Agenda.
    Hence I will work the table in Access. But when the new PO Agenda is received I want to copy all the comments and information added to the new PO Agenda. Hence the Query and Update above. Will the Insert command do that? I feel the Insert command would work if I am adding all the records. But the records only need to be updated. Your thoughts?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you are receiving a spreadsheet with new data and you need this new data imported into Access, that is different from updating records already in Access.

    If you want to import from Excel, do some edits, then export to Excel, that is another process altogether.

    So, I am still confused.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 10
    Last Post: 08-20-2018, 11:49 AM
  2. Syntax Error in UPDATE Query
    By lamore48 in forum Access
    Replies: 7
    Last Post: 02-19-2018, 12:04 PM
  3. Syntax error in UPDATE query
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 02-14-2018, 12:38 PM
  4. Replies: 2
    Last Post: 10-09-2014, 10:53 AM
  5. Syntax Error 3144 in SQL Update Query.
    By Phred in forum Programming
    Replies: 4
    Last Post: 03-02-2012, 02:39 PM

Tags for this Thread

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