Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Syntax trouble using IIF in a query (2000)

    I would be pulling my hair out right now... but I'm already bald! I am working on a database that has an amount field - the users enter an amount, and then use a combo box to choose DR or CR to indicate whether it's a debit or credit amount. I was asked to give the users an option to report just debits, credits, or both debits and credits. I'm using a triple-state toggle button, and testing for a value of -1, 0 or anything else, with:

    IIf([forms]![frmMultLevelRptSelector].[tglDR_CR_Both]=-1,"DR",IIf([forms]![frmMultLevelRptSelector].[tglDR_CR_Both]=0,"CR","Is Not Null"))

    Instead of "Is Not Null" for the 'other' value, I've tried "DR or CR", ""DR" or "CR"", "*", "[*]", and probably several others. For some, I get the message that it's too complex, on others I get an empty recordset.

    The query that I'm working on feeds some of the reports directly, or is a source for other queries that feed other reports. I know that I probably can test the toggle button from the OnLoad event for the reports to filter what I want (I hope!), but thought I'd try to do it only once in the sorce for all of the reports.

    Can anyone get me heading in the right direction with this?

    Thanks,

    Howard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax trouble using IIF in a query (2000)

    Try

    Like IIf([forms]![frmMultLevelRptSelector].[tglDR_CR_Both]=-1,"DR",IIf([forms]![frmMultLevelRptSelector].[tglDR_CR_Both]=0,"CR","*"))

    Without Like, the condition is the same as =IIf(....). The condition ="DR" is valid, but ="Is Not Null" or ="*" are not. The condition Like "DR" is equivalent to ="DR", while Like "*" means that you get everything.

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Syntax trouble using IIF in a query (2000)

    Thank you, thank you, thank you! I hadn't thought of that (obvoiusly), but probably tried everything else but that. I appreciate your quick response.

    Regards,

    Howard

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Syntax trouble using IIF in a query (2000)

    I pasted your expression into a query and it worked perfectly.
    It shouldn't matter what expression you put in the final pair of brackets because it is just returning a string.

    I suspect that the problem is not the expression itself, but what you are trying to do with it. How are you using the expression? Is it on the criteria line?
    Regards
    John



Posting Permissions

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