Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100

    Unanswered: Nested IIf Statements for UPDATE

    I've successfully created nested IIf statements in the past to update fields with new values, based on pre-existing values. The problem I'm having now is setting a new value in a blank field based on date values from an exisiting field using < and >.

    Here's my non-working UPDATE statement:
    Code:
    UPDATE OLS SET OLS.MULTIPLE = IIf(OLS.FPDate<="200401,'60',IIf(OLS.FPDate<'200501' AND OLS.FPDate>'200401','48','36'))
    WHERE OLS.FPDate IS NOT NULL
    What I'm trying to do is set purchase multiples based on dates. Basically anything older than 3 years at 60 months, 2-3 years at 48 months, 1 year or less at 36 months.

    If I should think about constructing this differently please let me know. Thanks!
    BillS

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    What is the data type of OLS.FPDate?

  3. #3
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Unfortunately it's TEXT.
    BillS

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You have a small error in the script. There is a " missing after the first 200401

    Also, your MULTIPLE field must be data type text.

    hth
    Chris

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try this:

    IIf(12*(Year(Date())-CInt(Left(OLS.FPDate,4)))+(Month(Date())-CInt(Right(OLS.FPDate,Len(OLS.FPDate)-4)))<12,36,IIf(12*(Year(Date())-CInt(Left(OLS.FPDate,4)))+(Month(Date())-CInt(Right(OLS.FPDate,Len(OLS.FPDate)-4)))<36,48,60))

  6. #6
    Join Date
    Jun 2005
    Location
    Denver, CO
    Posts
    100
    Thanks for your help, actually all I had to do was fix the " in my first posting...dope!
    Last edited by BillSinc; 06-05-06 at 15:34.
    BillS

Posting Permissions

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