Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: Increment field using update query

    Hi,

    I am trying to run an update query based on some criteria I want to increment a field by 100. For eg. Points=0...If Name=tom then Points=Points+100.

    The problem is it remains at 100. It wont increment. Right now in the update field I have "+100" as the increment factor. I tried using "Points+100" but that gives me a type conversion failure. Points currently is a double. Do I need to change that? Or am I doing something else wrong?

    Thanks for your help.
    MKK

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you post the whole sub?

    The syntax you've shown is ok, you shouldn't be having problems there.

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    Im sorry Im new to this stuff...what do you mean sub? My query? If yes it is

    UPDATE (Main INNER JOIN Sales_Consultants ON Main.PIN = Sales_Consultants.PIN) INNER JOIN Dealer ON Main.DealerNo = Dealer.DealerNo SET Sales_Consultants.GKL2_Points = "GKL2_Points"+100, Dealer.GKL2_Points = "GKL2_Points"+100
    WHERE (((Main.Contract_Type)="GKL") AND ((Main.Term)>"36") AND ((Main.New_or_Used)="New"));

    Thanks

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    "GKL2_Points"+100

    That's your problem

    By encapsulating that in quotes, you are explicitly defining it as a string. Therefore you are trying to add an integer to a string. Try this:

    UPDATE (Main INNER JOIN Sales_Consultants ON Main.PIN = Sales_Consultants.PIN) INNER JOIN Dealer ON Main.DealerNo = Dealer.DealerNo SET Sales_Consultants.GKL2_Points = GKL2_Points+100, Dealer.GKL2_Points = GKL2_Points+100
    WHERE (((Main.Contract_Type)="GKL") AND ((Main.Term)>36) AND ((Main.New_or_Used)="New"));

  5. #5
    Join Date
    Mar 2004
    Posts
    16
    It wont let me do that. As soon as I tab out of the field it puts the quotes in.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by mkk
    It wont let me do that. As soon as I tab out of the field it puts the quotes in.
    Try cutting and pasting the query directly into SQL view.

    If that doesn't work, then the update query isn't the problem, it's somewhere else.

  7. #7
    Join Date
    Mar 2004
    Posts
    16
    I got it working...I just need to specify the table along with the field. [Sales_Consultants].[Retail_Points]+100 makes it work fine.

    Thanks for your help!

    Regards,
    MKK

Posting Permissions

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