I need a little help (maybe a lot J) … I am stuck with this statement.



This is in an update query
Two linked tables by the ProductLine
How can I fix this statement to work? And where does it need to be entered/added?
Code:
([MonthNo]=Month(Date())=IIf([MP2_ProjDemandMonths].[NoOfMonths]=0,[MP2_ProductLineNoOfMonths].[NoOfMonths],[MP2_ProjDemandMonths].[NoOfMonths])

I’ll explain a little more …

MonthNo field in table MP2_ProjDemandMonths has months 1-24 representing a 24 month period January of current year to December of Next Year.


NoOfMonths field is in both tables. One at the top level by just Product Line and another specifically by Item.

The Item level will supercede the ProductLine level.

It’ll update for all the items in the product line for the number of months except when an specific item has number of months, then it’ll use that value to update.

What is it updating? It's the value in the Qty field for each month (MonthNo)


The end result:
Today is the current month 4
The user entered 8 months for the NoOfMonths to change / update.

The update query needs to know what the value is in the MonthNo 4 which is 100.

The update query will take the qty 100 from MonthNo 4 and update the next 8 consecutive months. MonthNo’s 5-12 and change the qty to 100 regardless of the qty in there now.

Except if the Item also has a NoOfMonths value from the Item level, in above example it’s 3 months. In this case it’ll take the value in MonthNo 7 and update the next consecutive 3 months to 50 instead of the 100 from the ProductLine level number of months.

:cry: