Problem with Update Query

G

Guest

Hello,

I have the following in a query:

UPDATE Actuals INNER JOIN [Forecast - Dynamic] ON (Actuals.[JDE Bus Unit] =
[Forecast - Dynamic].[JDE Bus Unit]) AND (Actuals.[JDE GL] = [Forecast -
Dynamic].[JDE GL]) SET [Forecast - Dynamic].Jan =
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]), [Forecast -
Dynamic].Feb = IIf(Month(Forms!Main![Fiscal Month])>=2,[C-Feb Actual],[Feb]),
[Forecast - Dynamic].Mar = IIf(Month(Forms!Main![Fiscal Month])>=3,[C-Mar
Actual],[Mar]), [Forecast - Dynamic].Apr = IIf(Month(Forms!Main![Fiscal
Month])>=4,[C-Apr Actual],[Apr]), [Forecast - Dynamic].May =
IIf(Month(Forms!Main![Fiscal Month])>=5,[C-May Actual],[May]), [Forecast -
Dynamic].Jun = IIf(Month(Forms!Main![Fiscal Month])>=6,[C-Jun Actual],[Jun]),
[Forecast - Dynamic].Jul = IIf(Month(Forms!Main![Fiscal Month])>=7,[C-Jul
Actual],[Jul]), [Forecast - Dynamic].Aug = IIf(Month(Forms!Main![Fiscal
Month])>=8,[C-Aug Actual],[Aug]), [Forecast - Dynamic].Sep =
IIf(Month(Forms!Main![Fiscal Month])>=9,[C-Sep Actual],[Sep]), [Forecast -
Dynamic].Oct = IIf(Month(Forms!Main![Fiscal Month])>=10,[C-Oct
Actual],[Oct]), [Forecast - Dynamic].Nov = IIf(Month(Forms!Main![Fiscal
Month])>=11,[C-Nov Actual],[Nov]), [Forecast - Dynamic].[Dec] =
IIf(Month(Forms!Main![Fiscal Month])>=12,[C-Dec Actual],[Dec]);


Now...when I close out of the form ....and I go back into design view....all
the field names in the grid are "expr1", "exp2" like it can't find the fields
"Jan" "Feb" "Mar" etc etc from the Forecast-DYnamic table. I cannot fathom
why this is happening.

Please advise!!

Thanks,
MN
 
J

John Spencer

This is an expression and does not have a name, so Access defaults to Expr#.
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),

If you wanted to assign a "name" - an alias - to the expression you need to
do so
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) AS JanCalc

Unless you specifically refer to the fields by the tablename.fieldname
syntax you probably will not be able to use
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) as JAN
since Access will not be able to resolve which JAN you mean - the field or
the expression.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

I tried putting "as Jan" in the update to: field of the query and it will not
let me....says 'invalid syntax'.....i have "entered an operand without an
operator" -- doesn't like the "as" in there.

John Spencer said:
This is an expression and does not have a name, so Access defaults to Expr#.
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),

If you wanted to assign a "name" - an alias - to the expression you need to
do so
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) AS JanCalc

Unless you specifically refer to the fields by the tablename.fieldname
syntax you probably will not be able to use
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) as JAN
since Access will not be able to resolve which JAN you mean - the field or
the expression.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

MacNut2004 said:
Hello,

I have the following in a query:

UPDATE Actuals INNER JOIN [Forecast - Dynamic] ON (Actuals.[JDE Bus Unit]
=
[Forecast - Dynamic].[JDE Bus Unit]) AND (Actuals.[JDE GL] = [Forecast -
Dynamic].[JDE GL]) SET [Forecast - Dynamic].Jan =
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]), [Forecast -
Dynamic].Feb = IIf(Month(Forms!Main![Fiscal Month])>=2,[C-Feb
Actual],[Feb]),
[Forecast - Dynamic].Mar = IIf(Month(Forms!Main![Fiscal Month])>=3,[C-Mar
Actual],[Mar]), [Forecast - Dynamic].Apr = IIf(Month(Forms!Main![Fiscal
Month])>=4,[C-Apr Actual],[Apr]), [Forecast - Dynamic].May =
IIf(Month(Forms!Main![Fiscal Month])>=5,[C-May Actual],[May]), [Forecast -
Dynamic].Jun = IIf(Month(Forms!Main![Fiscal Month])>=6,[C-Jun
Actual],[Jun]),
[Forecast - Dynamic].Jul = IIf(Month(Forms!Main![Fiscal Month])>=7,[C-Jul
Actual],[Jul]), [Forecast - Dynamic].Aug = IIf(Month(Forms!Main![Fiscal
Month])>=8,[C-Aug Actual],[Aug]), [Forecast - Dynamic].Sep =
IIf(Month(Forms!Main![Fiscal Month])>=9,[C-Sep Actual],[Sep]), [Forecast -
Dynamic].Oct = IIf(Month(Forms!Main![Fiscal Month])>=10,[C-Oct
Actual],[Oct]), [Forecast - Dynamic].Nov = IIf(Month(Forms!Main![Fiscal
Month])>=11,[C-Nov Actual],[Nov]), [Forecast - Dynamic].[Dec] =
IIf(Month(Forms!Main![Fiscal Month])>=12,[C-Dec Actual],[Dec]);


Now...when I close out of the form ....and I go back into design
view....all
the field names in the grid are "expr1", "exp2" like it can't find the
fields
"Jan" "Feb" "Mar" etc etc from the Forecast-DYnamic table. I cannot
fathom
why this is happening.

Please advise!!

Thanks,
MN
Click to expand...
Click to expand...
 
J

John Spencer

Whoops! I wasn't thinking. You can't alias the UPDATE to Values. My goof.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I tried putting "as Jan" in the update to: field of the query and it will not
let me....says 'invalid syntax'.....i have "entered an operand without an
operator" -- doesn't like the "as" in there.

John Spencer said:
This is an expression and does not have a name, so Access defaults to Expr#.
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),

If you wanted to assign a "name" - an alias - to the expression you need to
do so
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) AS JanCalc

Unless you specifically refer to the fields by the tablename.fieldname
syntax you probably will not be able to use
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) as JAN
since Access will not be able to resolve which JAN you mean - the field or
the expression.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

MacNut2004 said:
Hello,

I have the following in a query:

UPDATE Actuals INNER JOIN [Forecast - Dynamic] ON (Actuals.[JDE Bus Unit]
=
[Forecast - Dynamic].[JDE Bus Unit]) AND (Actuals.[JDE GL] = [Forecast -
Dynamic].[JDE GL]) SET [Forecast - Dynamic].Jan =
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]), [Forecast -
Dynamic].Feb = IIf(Month(Forms!Main![Fiscal Month])>=2,[C-Feb
Actual],[Feb]),
[Forecast - Dynamic].Mar = IIf(Month(Forms!Main![Fiscal Month])>=3,[C-Mar
Actual],[Mar]), [Forecast - Dynamic].Apr = IIf(Month(Forms!Main![Fiscal
Month])>=4,[C-Apr Actual],[Apr]), [Forecast - Dynamic].May =
IIf(Month(Forms!Main![Fiscal Month])>=5,[C-May Actual],[May]), [Forecast -
Dynamic].Jun = IIf(Month(Forms!Main![Fiscal Month])>=6,[C-Jun
Actual],[Jun]),
[Forecast - Dynamic].Jul = IIf(Month(Forms!Main![Fiscal Month])>=7,[C-Jul
Actual],[Jul]), [Forecast - Dynamic].Aug = IIf(Month(Forms!Main![Fiscal
Month])>=8,[C-Aug Actual],[Aug]), [Forecast - Dynamic].Sep =
IIf(Month(Forms!Main![Fiscal Month])>=9,[C-Sep Actual],[Sep]), [Forecast -
Dynamic].Oct = IIf(Month(Forms!Main![Fiscal Month])>=10,[C-Oct
Actual],[Oct]), [Forecast - Dynamic].Nov = IIf(Month(Forms!Main![Fiscal
Month])>=11,[C-Nov Actual],[Nov]), [Forecast - Dynamic].[Dec] =
IIf(Month(Forms!Main![Fiscal Month])>=12,[C-Dec Actual],[Dec]);


Now...when I close out of the form ....and I go back into design
view....all
the field names in the grid are "expr1", "exp2" like it can't find the
fields
"Jan" "Feb" "Mar" etc etc from the Forecast-DYnamic table. I cannot
fathom
why this is happening.

Please advise!!

Thanks,
MN
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

So then how do I solve this problem?

Thanks!

John Spencer said:
Whoops! I wasn't thinking. You can't alias the UPDATE to Values. My goof.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I tried putting "as Jan" in the update to: field of the query and it will not
let me....says 'invalid syntax'.....i have "entered an operand without an
operator" -- doesn't like the "as" in there.

John Spencer said:
This is an expression and does not have a name, so Access defaults to Expr#.
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),

If you wanted to assign a "name" - an alias - to the expression you need to
do so
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) AS JanCalc

Unless you specifically refer to the fields by the tablename.fieldname
syntax you probably will not be able to use
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) as JAN
since Access will not be able to resolve which JAN you mean - the field or
the expression.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have the following in a query:

UPDATE Actuals INNER JOIN [Forecast - Dynamic] ON (Actuals.[JDE Bus Unit]
=
[Forecast - Dynamic].[JDE Bus Unit]) AND (Actuals.[JDE GL] = [Forecast -
Dynamic].[JDE GL]) SET [Forecast - Dynamic].Jan =
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]), [Forecast -
Dynamic].Feb = IIf(Month(Forms!Main![Fiscal Month])>=2,[C-Feb
Actual],[Feb]),
[Forecast - Dynamic].Mar = IIf(Month(Forms!Main![Fiscal Month])>=3,[C-Mar
Actual],[Mar]), [Forecast - Dynamic].Apr = IIf(Month(Forms!Main![Fiscal
Month])>=4,[C-Apr Actual],[Apr]), [Forecast - Dynamic].May =
IIf(Month(Forms!Main![Fiscal Month])>=5,[C-May Actual],[May]), [Forecast -
Dynamic].Jun = IIf(Month(Forms!Main![Fiscal Month])>=6,[C-Jun
Actual],[Jun]),
[Forecast - Dynamic].Jul = IIf(Month(Forms!Main![Fiscal Month])>=7,[C-Jul
Actual],[Jul]), [Forecast - Dynamic].Aug = IIf(Month(Forms!Main![Fiscal
Month])>=8,[C-Aug Actual],[Aug]), [Forecast - Dynamic].Sep =
IIf(Month(Forms!Main![Fiscal Month])>=9,[C-Sep Actual],[Sep]), [Forecast -
Dynamic].Oct = IIf(Month(Forms!Main![Fiscal Month])>=10,[C-Oct
Actual],[Oct]), [Forecast - Dynamic].Nov = IIf(Month(Forms!Main![Fiscal
Month])>=11,[C-Nov Actual],[Nov]), [Forecast - Dynamic].[Dec] =
IIf(Month(Forms!Main![Fiscal Month])>=12,[C-Dec Actual],[Dec]);


Now...when I close out of the form ....and I go back into design
view....all
the field names in the grid are "expr1", "exp2" like it can't find the
fields
"Jan" "Feb" "Mar" etc etc from the Forecast-DYnamic table. I cannot
fathom
why this is happening.

Please advise!!

Thanks,
MN
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I'm not sure you can. Perhaps someone else will understand your problem
differently than I did and will have a solution for you.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

MacNut2004 said:
So then how do I solve this problem?

Thanks!

John Spencer said:
Whoops! I wasn't thinking. You can't alias the UPDATE to Values. My
goof.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I tried putting "as Jan" in the update to: field of the query and it
will not
let me....says 'invalid syntax'.....i have "entered an operand without
an
operator" -- doesn't like the "as" in there.

:

This is an expression and does not have a name, so Access defaults to
Expr#.
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),

If you wanted to assign a "name" - an alias - to the expression you
need to
do so
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) AS
JanCalc

Unless you specifically refer to the fields by the tablename.fieldname
syntax you probably will not be able to use
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]) as
JAN
since Access will not be able to resolve which JAN you mean - the
field or
the expression.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hello,

I have the following in a query:

UPDATE Actuals INNER JOIN [Forecast - Dynamic] ON (Actuals.[JDE Bus
Unit]
=
[Forecast - Dynamic].[JDE Bus Unit]) AND (Actuals.[JDE GL] =
[Forecast -
Dynamic].[JDE GL]) SET [Forecast - Dynamic].Jan =
IIf(Month(Forms!Main![Fiscal Month])>=1,[C-Jan Actual],[Jan]),
[Forecast -
Dynamic].Feb = IIf(Month(Forms!Main![Fiscal Month])>=2,[C-Feb
Actual],[Feb]),
[Forecast - Dynamic].Mar = IIf(Month(Forms!Main![Fiscal
Month])>=3,[C-Mar
Actual],[Mar]), [Forecast - Dynamic].Apr =
IIf(Month(Forms!Main![Fiscal
Month])>=4,[C-Apr Actual],[Apr]), [Forecast - Dynamic].May =
IIf(Month(Forms!Main![Fiscal Month])>=5,[C-May Actual],[May]),
[Forecast -
Dynamic].Jun = IIf(Month(Forms!Main![Fiscal Month])>=6,[C-Jun
Actual],[Jun]),
[Forecast - Dynamic].Jul = IIf(Month(Forms!Main![Fiscal
Month])>=7,[C-Jul
Actual],[Jul]), [Forecast - Dynamic].Aug =
IIf(Month(Forms!Main![Fiscal
Month])>=8,[C-Aug Actual],[Aug]), [Forecast - Dynamic].Sep =
IIf(Month(Forms!Main![Fiscal Month])>=9,[C-Sep Actual],[Sep]),
[Forecast -
Dynamic].Oct = IIf(Month(Forms!Main![Fiscal Month])>=10,[C-Oct
Actual],[Oct]), [Forecast - Dynamic].Nov =
IIf(Month(Forms!Main![Fiscal
Month])>=11,[C-Nov Actual],[Nov]), [Forecast - Dynamic].[Dec] =
IIf(Month(Forms!Main![Fiscal Month])>=12,[C-Dec Actual],[Dec]);


Now...when I close out of the form ....and I go back into design
view....all
the field names in the grid are "expr1", "exp2" like it can't find
the
fields
"Jan" "Feb" "Mar" etc etc from the Forecast-DYnamic table. I cannot
fathom
why this is happening.

Please advise!!

Thanks,
MN
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update To line in a Query -- incorrect results!! 4
IIf Statement in "Update To" field of Update Query 6
Calulating FY Totals 3
Qry Running Average 5
Forecasting with formulas HELP 1
Serious help needed for this project Can anyone help me with this 7
Single Query 6
Fiscal year 2

Top