Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I can't find my notes for the syntax in a query.

    If a field is null or 0 I need to change it to "UNKNOWN"

    Is it an IIF statement ? ages since I messed about with Access.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The folowing will return UNKNOWN if the field is either 0 or NULL

    iif(isnull([field])or[field]=0,"UNKNOWN",[field])

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thaks for the help but I am getting "Circular Reference caused by Alias... "

    RepairCosts: IIf(IsNull([RepairCosts]) Or [RepairCosts]=0,"UNKNOWN",[RepairCosts])

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to give the column the same name as a field used in the expression, you must specify the table or query name for the field:

    RepairCosts: IIf(IsNull([NameOfTable].[RepairCosts]) Or [NameOfTable].[RepairCosts]=0,"UNKNOWN",[NameOfTable].[RepairCosts])

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Cheers guy's

    Works perfect.

Posting Permissions

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