UPDATE TO IIF Statement

[anilytics]

Hello all once again....



Ok I have 2 tables which structured identically but with different naming convention.

Table 1 - Is new data
Table 2 - Updated table

So Table 2 has values in there some of the values have been fixed since the begginging of the year.
Table 1 is where users use to input new values.

What I need to do in an update query with a lot of IIF statements depending on the values.

There are these 3 statements:

1) IIF Table 2 >= Table 1 VALUE = REMAIN AS Table 2
2) IIF Table 1 = Blank or 0 VALUE = REMAIN AS Table 2
3) IIF Table 1 >= Table 2 VALUE = Table 1

So I have had a few attempts:

Attempt 1:
IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast])

Attempt 2:
IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales]) or IIF(not isnull([mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales])) IIF ([mro_tblchanwave].[respforecast]>[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast]))


Attempt 3:
IIF([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast] OR IIF(not isnull(mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales])), IIF([mro_tblchanwave].[respforecast] > [forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast]))



But not much luck in making all 3 work!

your help would be appreciated!


Anil

[June7]

Missing the closing paren for the IsNull function.

Try:

IIF([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast] OR Not IsNull(mro_tblchanwave].[respforecast]),[forecast_channels].[forecastsales], IIF([mro_tblchanwave].[respforecast] > [forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast], "alternate value here"))

or:

IIF([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast] OR Not IsNull(mro_tblchanwave].[respforecast]),[forecast_channels].[forecastsales], [mro_tblchanwave].[respforecast])

[anilytics]

Thanks for the update.... I have added another "Or Not Isnull" to the second IIF parameter but still no luck it just does not want to update... even testing by adding a value higher than the other table.... I have inserted some snapshots of the two tables and the update query table and the new IIF statement syntax....




The new IIF Syntax is:

IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast] Or Not IsNull([mro_tblchanwave].[respforecast]),[forecast_channels].[forecastsales],IIf([mro_tblchanwave].[respforecast]>=[forecast_channels].[forecastsales] Or Not IsNull([forecast_channels].[forecastsales]),[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales]))

[anilytics]

I have also taken out the "Or Not Isnull" and still no joy....

IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales],IIf([mro_tblchanwave].[respforecast]>=[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales]))

[anilytics]

I changed:

IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales],IIf([mro_tblchanwave].[respforecast]>=[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales]))

To

IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales],IIf([mro_tblchanwave].[respforecast]>=[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast],"0"))

And when i run the update it produces the false answer which is "0". But when I then enter in figures into the table that gets updated by another system then run the update it works???

[anilytics]

Problem solved i used the following

IIf([forecast_channels].[forecastsales]>=[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales],IIf([mro_tblchanwave].[respforecast]>[forecast_channels].[forecastsales],[mro_tblchanwave].[respforecast],[forecast_channels].[forecastsales]))