Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update query (Access 2000)

    How can i build the update query ? In my query i have chosen the option update, query and under the field ASurcharge, whcih is a calculated field,i have written for "update to" : [products.surcharge]. Surcharge is the name of the field in the table products.
    But it leads to errors. Which is the right way ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    Try
    <code>
    [products].[surcharge]
    </code>
    If that doesn't help: what does the error message say?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    It says

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    What is the data type of the field you're trying to update?

    If you wish, you can attach a stripped down and zipped copy of your database.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Tahnk you. The data type of the field should be Currency.I am sending the zipped copy of the query

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    The query in your database works fine when I try it, but it is a select query, not an update query. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    I am sorry i made a mistake in my attachement.Actually the field with the calculation should be called Surcharge1 and it is the empty field in the table that is called surcharge.I want that that field surcharge should not be calculated and for that reason i want to update the fields surcharge1 to surcharge.I am sending again the corrected version of the query. Is it possible to do it from code so that not to create a field surcharge1 at all ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update query (Access 2000)

    You should create an update query. I thought that you had already done that, from the subject of your post, but it's still a select query.
    Select Query | Update Query to change the query type.
    Then enter the IIf expression

    IIf([Size]=1;0,138;IIf([Size]=0,4;0,138;IIf([Size]=0,5;0,138;IIf([Size]=4;0,552;IIf([Size]=10;0,552;IIf([Size]=5;0,552;IIf([Size]=18;2,48;IIf([size]=20;2,66;IIf([size]=60;6,27;IIf([size]=180;6,18;IIf([size]=205;19;IIf([size]=210;19;0))))))))))))/IIf([size]<5;1;[size])

    in the Update to line for the Surcharge column. (Note: do *not* include Surcharge1: in the expression).
    Delete the Surcharge1 column.
    Select Query | Run or click the Run button to execute the query.

    The disadvantage of this approach is that you will have to run the update query again when you edit or add a record in the products table - each time! It would be much better to use a separate table to look up the percentage, and to calculate the surcharge in a query.

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    It works so fine now and i am very grateful fo that ! I have followed your instruction and now i have a perfect query. Thank you

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update query (Access 2000)

    Dear Hans

    The query realy works fine.I have decided now to remove the field Surcharge and to update the field DDU. But here agan i have problems.Perhaps it is better to send it with a new 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
  •