Update query, nested iif SET and joined tables

Question

Trying to make an Update Query with inner join and have the SET reflect the current month number.

Need help with syntax on updating a joined table that has columns for months, need to update the months that are Month(DATE()) +1,+2 only. Trying to use the below nested IIF as "IIF(Month(Date()) ="1",( SET table.c = table2.c2......) complete code below.

Syntax error is the result

Sample data to update sql table linked excel with values to use for updates

Update dbo_MasterSalesForecast_Test

INNER JOIN MasterSalesForecastUpdate

ON (dbo_MasterSalesForecast_Test.SubTo=MasterSalesForecastUpdate.Item) AND( dbo_MasterSalesForecast_Test.Planner=MasterSalesForecastUpdate.Planner)

IIF(Month(Date()) ="1",( SET

dbo_MasterSalesForecast_Test.[YY:1] = [MasterSalesForecastUpdate].[YYJan],

dbo_MasterSalesForecast_Test.[YY:2] = [MasterSalesForecastUpdate].[YYFan],

dbo_MasterSalesForecast_Test.[YY:3] = [MasterSalesForecastUpdate].[YYMar])

,

IIF(Month(Date())="2",( SET

dbo_MasterSalesForecast_Test.[YY:2]=[MasterSalesForecastUpdate].[YYFeb],

dbo_MasterSalesForecast_Test.[YY:3]=[MasterSalesForecastUpdate].[YYMar],

dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr])

,

IIF(Month(Date())="3",( SET

dbo_MasterSalesForecast_Test.[YY:3]=[MasterSalesForecastUpdate].[YYMar],

dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr],

dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay])

,

IIF(Month(Date())="4",(SET

dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr],

dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay],

dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun])

,

IIF(Month(Date())="5",(SET

dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay],

dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun],

dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul])

,

IIF(Month(Date())="6",(SET

dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun],

dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul],

dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug])

,

IIF(Month(Date())="7",(SET

dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul],

dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug],

dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep])

,

IIF(Month(Date())="8",(SET

dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug],

dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep],

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct])

,

IIF(Month(Date())="9",(SET

dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep],

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov])

,

IIF(Month(Date())="10",(SET

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec])

,

IIF(Month(Date())="11",(SET

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],

dbo_MasterSalesForecast_Test.[YY+1:1]=[MasterSalesForecastUpdate].[YY+1Jan])

,

IIF(Month(Date())="12"(SET

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],

dbo_MasterSalesForecast_Test.[YY+1:1]=[MasterSalesForecastUpdate].[YY+1Jan],

dbo_MasterSalesForecast_Test.[YY+1:02]=[MasterSalesForecastUpdate].[YY+1Feb])



)))))))))));

Edit question to show sample data and desired result. Columns for months sounds like non-normalized data structure. What happens when you run this? Might be easier to accomplish in VBA. — June7
added table snips - get a syntax error with the mess of nested iifs. — frolord81
Data should be provided as text tables in question, not linked files or images. Or go to a forum where attaching files is encouraged. — June7
For one thing, Month(Date()) will return a number so don't put criteria in quote marks. IIF(Month(Date())=1,(. Also, don't repeat SET within each IIf(), it should be used only once in SQL statement outside IIf(). Is data in SQLServer? — June7
In Access I would use Choose() instead of IIf for this situation. Often Switch() is preferable to IIf(). SQLServer uses CASE(). But I am not even sure the fields can be conditional. I will have to experiment. — June7

June7 June7 · Accepted Answer · 2020-06-30T22:35:05

Cannot conditionally reference fields. Field must be static in query object then value to update with can be determined in an IIf() for each field. Use VBA to build and execute action SQL statement.

CurrentDb.Execute "UPDATE dbo_MasterSalesForecast_Test " & _
     "INNER JOIN MasterSalesForecastUpdate " & _
     "ON (dbo_MasterSalesForecast_Test.SubTo=MasterSalesForecastUpdate.Item) " & _
          "AND (dbo_MasterSalesForecast_Test.Planner=MasterSalesForecastUpdate.Planner) " & _
     "SET [YY:" & Month(Date()) & "] = [YY" & Format(Date(),"mmm") & "]," & _
         "[YY:" & Month(DateAdd("m",1,Date())) & "] = [YY" & Format(DateAdd("m",1,Date()),"mmm") & "]," & _
         "[YY:" & Month(DateAdd("m",2,Date())) & "] = [YY" & Format(DateAdd("m",2,Date()),"mmm") & "]"

Update query, nested iif SET and joined tables

1 Answers