Search code examples
datems-accessexpressionbuilder

Count Iff [date] occured in the past 90 days ACCESS

I'm having a bit of trouble.

I am trying to build a Access query that counts the number of times a product was consumed based on if the date occurred with in the past 90 days and the status of the product being consumed.

I attempted this for just counting the occurrences for the past 90 days. 

90 Days Consumption: Count(IIf([Movement]![Date]>Date()+90,[Movement]![Product],0))

It did not work. I'm not really sure how to also add the condition of it being the consumed status along with the past 90 days.

Anything helps.

Solution

  • "past 90 days" is today-90

    90 Days Consumption: Count(IIf([Movement]![Date]>Date()-90,[Movement]![Product],0))

    You might need to be careful with using COUNT() here because this function counts ANY non-null as 1, so the IIF() can return zero, which is non-null. Perhaps this instead:

    90 Days Consumption: Sum(IIf([Movement]![Date]>Date()-90,1,0))