Results 1 to 9 of 9
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: No Specified Expression (2000 SR-1)

    Try this:

    YOY: IIf([Jan 2001] Is Null Or 0,0,[ThisYearGA]/[YearAgoGA])
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: No Specified Expression (2000 SR-1)

    ...Sorry, I re-read your statement and believe the last post will still give you an incorrect answer.

    Try the following:

    YOY: IIf([Jan 2001] Is Null,0,IIf([jan 2001]=0,0,[ThisYearGA]/[YearAgoGA]))

    This will give you a zero if [Jan 2001] is null or equal to zero, else [ThisYearGA]/[YearAgoGA]
    Regards,

    Gary
    (It's been a while!)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Specified Expression (2000 SR-1)

    I am still getting the same error with all the options.

    What would cause this error? Do you understand it?
    There is always a way.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Specified Expression (2000 SR-1)

    I have an IIf in a query like this...

    YOY: IIf(IsNull([Jan 2001]) Or [Jan 2001]=0,0, [ThisYearGA]/[YearAgoGA])
    Expression

    The error I get is...
    You tried to execute a query that does not include the specified expression 'YOY: IIf(Not IsNull([Jan 2001]) Or [Jan 2001]=0,0,[ThisYearGA]/[YearAgoGA])' as part of an aggregate function

    I have lived throught this in the past but can not seen to get past it now.
    Any thoughts that can explain this to me will help.
    There is always a way.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Specified Expression (2000 SR-1)

    This is related to the group by of the query.
    Can you post the syntax of the query ?
    Francois

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: No Specified Expression (2000 SR-1)

    Is there any chance you are trying to do this on a query that is grouping data for a subtotal or contains other calculated fields?
    Regards,

    Gary
    (It's been a while!)

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Specified Expression (2000 SR-1)

    Much more than a chance...
    YearAgoGA: ([Jan 2001]+[Feb 2001]+[Mar 2001])/3
    ThisYearGA: ([Jan 2002]+[Feb 2002]+[Mar 2002])/3

    -both 'group by'
    then...

    YOY: IIf(IsNull([Jan 2001]) Or [Jan 2001]=0,0,[ThisYearGA]/[YearAgoGA])
    -expression
    There is always a way.

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: No Specified Expression (2000 SR-1)

    Try creating a new query that uses the group by query as its recordset. Put YOY in the new query so it is not effected by the group by or other calculated fields. This should eliminate the error.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Specified Expression (2000 SR-1)

    You're going to have to repeat the calculations in your IIF if you don't want to use a second query based on the group by query.
    Charlotte

Posting Permissions

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