Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Error using the IIF Function

    I am getting the following error as a result of me changing my IIF Function to test between to table items.

    You tried to execute a query that does not include the specified expression 'IIf(([ABC Credits]![Date Last Updated]=tblQuarterlyInfo!FirstMonth),(Count([ABC Credits]![Account])),0)' as part of an aggregate function.

    The table tblQuarterlyInfo contains only 1 record while the table ABC Credits contains may records.

    Here is the complete SQL statement

    SELECT IIf(([ABC Credits]![Date Last Updated]=tblQuarterlyInfo!FirstMonth),(Count([ABC Credits]![Account])),0) AS Month1 INTO [Temp Quarterly Credit summary]
    FROM [ABC Credits], tblQuarterlyInfo;

    The query works fine when I replace tblQuarterlyInfo!FirstMonth with an actual date such as #1/30/2004#. However, this is a query in a long set of queries that is part of a Macro and it would be more useful if I could pull the date out of the table tblQuarterlyInfo!FirstMonth field. There is only 1 record in the table tblQuarterly

    I am relatively new to programming, so as you would expect this simply blows me away. Lost and confused.

  2. #2
    Join Date
    Dec 2003
    Posts
    28
    This is my first attempt to help someone here, I usually jsut come for help, but I think I got this one. Instead of Macros, use VB and build the query string like:

    docmd.RunSQL "SELECT IIf(([ABC Credits]![Date Last Updated]=" & tblQuarterlyInfo!FirstMonth & "),(Count([ABC Credits]![Account])),0) AS Month1 INTO [Temp Quarterly Credit summary]
    FROM [ABC Credits], tblQuarterlyInfo;"

    That will insert the value at run time.

  3. #3
    Join Date
    Feb 2004
    Posts
    2

    Red face

    That is a great novel approach!! Thanks. It worked.!!!!

Posting Permissions

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