Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Thumbs down Unanswered: Update Query Frustration

    I am executing an update query in access as follows....
    UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO) SET tPriceSmart.AmtOverRide = IIf([DtePrice1]<=CDate(Date()),[AmtPrice1],IIf([DtePrice2]<=CDate(Date()),[AmtPrice2],0))
    WHERE (((tPriceRaw.IdPriceType)=8));

    Here's my stab at converting this to a stored proc.

    UPDATE (tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO)
    SET tPriceSmart.AmtOverRide =
    SELECT
    CASE
    WHEN DtePrice1 <= GetDate() THEN AmtPrice1
    ELSE (CASE WHEN (CASE DtePrice2 WHEN '00/00/0000' THEN '1/1/1900' END) <=GetDate() THEN AmtPrice2 ELSE 0 END)
    END
    WHERE tPriceRaw.IdPriceType=8

    I kknow the Join Statement works becasue it does work in the context of a select. I also know the case logic is working becasue I am using it in an Insert query elsewhere.

    Can't quite pull it together though. I am getting an error at Line #1 "(". I gave it a good try though before I resorted to throwing it into the forum. I am sure it is a stupid syntax error. Any help would be appreciated.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Update Query Frustration

    I think you have an error in the order of your statements and also you must put a table name after update. Try this (you might need to fix some syntax but the order of the statements should be like this):

    UPDATE tPriceSmart

    SET tPriceSmart.AmtOverRide =
    SELECT
    CASE
    WHEN DtePrice1 <= GetDate() THEN AmtPrice1
    ELSE (CASE WHEN (CASE DtePrice2 WHEN '00/00/0000' THEN '1/1/1900' END) <=GetDate() THEN AmtPrice2 ELSE 0 END)
    END

    FROM ( tPriceSmart INNER JOIN tPriceRaw ON tPriceSmart.IdProd = tPriceRaw.IdProd) INNER JOIN tCusMas ON (tPriceRaw.IdPriceSchd = tCusMas.CM_OVRIDE) AND (tPriceSmart.IdCust = tCusMas.CM_CUSTNO)

    WHERE tPriceRaw.IdPriceType=8
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    11

    Thanks

    Thanks for the input. After a long struggle, I did get it to work. Pretty much exactly as you stated....thanks again.

Posting Permissions

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