Because you have confused Access as to what table and field combination you
are working with in the query.
You must not leave any doubt as to the table/field.
Below I am assuming the table names to be Actuals and Forecast.
UPDATE Actuals INNER JOIN [Forecast] ON (Actuals.[ACCOUNT] =
[Forecast].[ACCOUNT]) AND (Actuals.[Unit Number] = [Forecast].[Unit Number])
SET [Forecast].Jan = IIf(Month(Forms!Main![Month])>=1,
[Actuals].[Jan],[Forecast].[Jan]), [Forecast].Feb =
IIf(Month(Forms!Main![Month])>=2,[Actuals].[Feb],[Forecast].[Feb]),
[Forecast].Mar =
IIf(Month(Forms!Main![Month])>=3,[Actuals].[Mar],[Forecast].[Mar]),
[Forecast].Apr =
IIf(Month(Forms!Main![Month])>=4,[Actuals].[Apr],[Forecast].[Apr]),
[Forecast].May =
IIf(Month(Forms!Main![Month])>=5,[Actuals].[May],[Forecast].[May]),
[Forecast].Jun =
IIf(Month(Forms!Main![Month])>=6,[Actuals].[Jun],[Forecast].[Jun]),
[Forecast].Jul =
IIf(Month(Forms!Main![Month])>=7,[Actuals].[Jul],[Forecast].[Jul]),
[Forecast].Aug =
IIf(Month(Forms!Main![Month])>=8,[Actuals].[Aug],[Forecast].[Aug]),
[Forecast].Sep =
IIf(Month(Forms!Main![Month])>=9,[Actuals].[Sep],[Forecast].[Sep]),
[Forecast].Oct =
IIf(Month(Forms!Main![Month])>=10,[Actuals].[Oct],[Forecast].[Oct]),
[Forecast].Nov =
IIf(Month(Forms!Main![Month])>=11,[Actuals].[Nov],[Forecast].[Nov]),
[Forecast].[Dec] =
IIf(Month(Forms!Main![Month])>=12,[Actuals].[Dec],[Forecast].[Dec]);
--
KARL DEWEY
Build a little - Test a little
MacNut2004 said:
Sorry, I was renaming fields so it was less confusing. The first post should
have read "Jan" instead of forecast jan. At any rate..why am i getting the
error i'm getting with this query?
KARL DEWEY said:
Your orignal post said -
IIf(Month(Forms!Main![Month])>=1,[Jan Actual],[Forecast Jan])
This post says -
IIf(Month(Forms!Main![Month])>=1,[Jan Actual],[Jan])
You seem to be mixing table and field names together. It looks like you
have tables Actual and Forecast with fields Jan, Feb, etc. You in turn used
[Forecast Jan] which is not a valid name. It would be valid as
[Forecast]![Jan] being field 'Jan' in table 'Forecast'.
--
KARL DEWEY
Build a little - Test a little
:
Here you go:
UPDATE Actuals INNER JOIN [Forecast] ON (Actuals.[ACCOUNT] =
[Forecast].[ACCOUNT]) AND (Actuals.[Unit Number] = [Forecast].[Unit Number])
SET [Forecast].Jan = IIf(Month(Forms!Main![Month])>=1,[Jan Actual],[Jan]),
[Forecast].Feb = IIf(Month(Forms!Main![Month])>=2,[Feb Actual],[Feb]),
[Forecast].Mar = IIf(Month(Forms!Main![Month])>=3,[Mar Actual],[Mar]),
[Forecast].Apr = IIf(Month(Forms!Main![Month])>=4,[Apr Actual],[Apr]),
[Forecast].May = IIf(Month(Forms!Main![Month])>=5,[May Actual],[May]),
[Forecast].Jun = IIf(Month(Forms!Main![Month])>=6,[Jun Actual],[Jun]),
[Forecast].Jul = IIf(Month(Forms!Main![Month])>=7,[Jul Actual],[Jul]),
[Forecast].Aug = IIf(Month(Forms!Main![Month])>=8,[Aug Actual],[Aug]),
[Forecast].Sep = IIf(Month(Forms!Main![Month])>=9,[Sep Actual],[Sep]),
[Forecast].Oct = IIf(Month(Forms!Main![Month])>=10,[Oct Actual],[Oct]),
[Forecast].Nov = IIf(Month(Forms!Main![Month])>=11,[Nov Actual],[Nov]),
[Forecast].[Dec] = IIf(Month(Forms!Main![Month])>=12,[Dec Actual],[Dec]);
:
Post your complete SQL statement.
--
KARL DEWEY
Build a little - Test a little
:
Hello,
I am having an issue with an update query and using an IF statement in the
"Update To" fields of that query. I have 2 tables in the query ("Actuals"
and "Forecast") -- 2 one-to-one joins on Unit Number and Account Number.
Down below in the query grid, I have Jan-Dec fields that need to be updated
to either the actualls for that month or the forecast values based on the
current month. Below is my if statement for the month of January, for
example:
IIf(Month(Forms!Main![Month])>=1,[Jan Actual],[Forecast Jan])
**(actuals come from one table, forecast numbers come from the other)
I have this IIF statement for every month in the query grid.
This works out great but as soon as I close the query and reopen it in
design....each of my fields in the grid come up as "Expr1: Forecast Jan" so
it is not finding each field for some reason.
Does anyone know why this is happening and if so, could help me?
Thank you in advance!
MN
