Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Problem with SQL

    Having problems with the following SQL Statement:

    SELECT [Code List].PRODUCT, [Code List].[LIFE], IIf([LIFE]<=12,11,13) AS [Months]
    FROM [Code List];

    If LIFE is zero or Null I want it to leave a Null in Months field but it is putting in 13...

    What can I do to fix it?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    IIf(IsNull([LIFE]),NULL,IIf([LIFE]<=12,11,13)) AS [Months]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Having problems with the following SQL Statement:

    SELECT [Code List].PRODUCT, [Code List].[LIFE], IIf([LIFE]<=12,11,13) AS [Months]
    FROM [Code List];

    If LIFE is zero or Null I want it to leave a Null in Months field but it is putting in 13...

    What can I do to fix it?
    Code:
    SELECT [Code List].PRODUCT, [Code List].[LIFE], IIf(Nz([LIFE],0)>0,11,0) AS [Months]
    FROM [Code List];
    will put in a "0",
    Code:
    SELECT [Code List].PRODUCT, [Code List].[LIFE], IIf(Nz([LIFE]," ")>" ",11," ") AS [Months]
    FROM [Code List];
    will put in a " ".

    Use one format or the other, depending if LIFE is a string or numeric. To my knowledge, you can never "put in" a null string in this manner, although you may want to experiment with it.

    Sam

  4. #4
    Join Date
    Nov 2003
    Posts
    300

    Smile

    The first suggestion worked perfect! Thanks!

Posting Permissions

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