If Query

[KarenWilson]

Morning
I am trying to create a query to calculate the Drop Dead Date of stock.
This is my query
DDD: IIf(Sum_12_Month_Stock_Movement![SumOfSumOfTransaction Quantity]>0,Date()+Count_Months_Stock!Months*30," No Usage Last 12 Months")





Where there is no stock in months a DDD is not calculated – any ideas, how I can get this to return Date()

[Rawb]

The only way I know would be to have a reference Table/Query that enumerates all possible values of Months. Then modify your Query to build a sum grouping off of those reference values instead of what's in your table.

That way you'll end up with a sum of 0 on the months values that don't have any Records for them and your IIf statement should work.

Create a temp Table (tblMonths) with two fields in it:
MonthID - Primary Key, autonumber
MonthNo - Integer

Then, right before you run the query, run the following Code:

  Dim rstTblMonth as Recordset

  Dim nbrCurrMonth as Integer

  Set rstTblMonth = CurrentDB().OpenRecordset("tblMonth", dbOpenDynaset)

  ' Empty out the temp Table
  DoCmd.SetWarnings False
  DoCmd.RunSQL "DELETE FROM tblMonth WHERE 1"
  DoCmd.SetWarnings True

  nbrCurrMonth = 0

  ' Add the previous 12 months to the table, including the current month
  Do While nbrCurrMonth < 12
    With rstTblMonth
      .AddNew

      ' grab the month of today minus nbrCurrMonth months
      !MonthNo = Month(DateAdd("m", -nbrCurrMonth,Date()))
      .Update
    End With

    ' increment our counter
    nbrCurrMonth = nbrCurrMonth + 1
  Loop

  rstTblMonth.Close

  Set rstTblMonth = Nothing

[KarenWilson]

I have managed to create a query that works

DDD: IIf(IsNull([SumOfSumOfTransaction Quantity]),"No Usage Last 12Months",(IIf(IsNull([Stock]) And [SumOfSumOfTransaction Quantity]>0,Date(),(IIf([Sum_12_Month_Stock_Movement]![SumOfSumOfTransaction Quantity]>0,Date()+[Count_Months_Stock]![Months]*30,"")))))

Turned the thinking around
If no transactions in 12 months no usage
If no stock and transactions > 0 then DDD today
else
calculate number of months of stock available

thanks for looking at this
I had been trying to work it out for ages, but need to take a new approach
karen