Hello,

I am using report wizard to build a report from a query. I receive an error
when I have the report wizard sum the gain_loss column of my query. The
syntax for the gain_loss column is below. The error reads, "The expression
is typed incorrectly, or it is too complex to be evaluated. For example, a
numeric expression may contain too many complicated elements. Try
simplifying the expression by assinging parts of the expression to
variables." I can run the query wtihout any error, but I cannot get the
repot wizard to sum the column. There is a nested if statement in my query.
I am not sure what to do. I am very new to Access.

IIf(fxsen.buy_CCY="USD",(IIf(fxsen.sell_ccy="EUR" Or
fxsen.sell_ccy="GBP",(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy*fxsen.settle_rate,(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy/fxsen.settle_rate)),(IIf(fxsen.buy_ccy="EUR"
Or
fxsen.buy="GBP",(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell*fxsen.settle_rate,(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell/fxsen.settle_rate))) AS Gain_Loss

Thanks for your help.
Regards,
John

You might try wrap the entire IIf(.....) in Val( IIf(.....) )

--
Duane Hookom
MS Access MVP


"spartanmba" <(E-Mail Removed)> wrote in message
news:097C40F9-1AFE-4A71-9407-(E-Mail Removed)...
> Hello,
>
> I am using report wizard to build a report from a query. I receive an
> error
> when I have the report wizard sum the gain_loss column of my query. The
> syntax for the gain_loss column is below. The error reads, "The
> expression
> is typed incorrectly, or it is too complex to be evaluated. For example,
> a
> numeric expression may contain too many complicated elements. Try
> simplifying the expression by assinging parts of the expression to
> variables." I can run the query wtihout any error, but I cannot get the
> repot wizard to sum the column. There is a nested if statement in my
> query.
> I am not sure what to do. I am very new to Access.
>
> IIf(fxsen.buy_CCY="USD",(IIf(fxsen.sell_ccy="EUR" Or
> fxsen.sell_ccy="GBP",(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy*fxsen.settle_rate,(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy/fxsen.settle_rate)),(IIf(fxsen.buy_ccy="EUR"
> Or
> fxsen.buy="GBP",(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell*fxsen.settle_rate,(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell/fxsen.settle_rate)))
> AS Gain_Loss
>
> Thanks for your help.
> Regards,
> John
>

Duane,

I tried wrapping the Iif() like you said in the query, but I still get the
same error message from the report. Thanks for your help.

John

"Duane Hookom" wrote:

> You might try wrap the entire IIf(.....) in Val( IIf(.....) )
>
> --
> Duane Hookom
> MS Access MVP
>
>
> "spartanmba" <(E-Mail Removed)> wrote in message
> news:097C40F9-1AFE-4A71-9407-(E-Mail Removed)...
> > Hello,
> >
> > I am using report wizard to build a report from a query. I receive an
> > error
> > when I have the report wizard sum the gain_loss column of my query. The
> > syntax for the gain_loss column is below. The error reads, "The
> > expression
> > is typed incorrectly, or it is too complex to be evaluated. For example,
> > a
> > numeric expression may contain too many complicated elements. Try
> > simplifying the expression by assinging parts of the expression to
> > variables." I can run the query wtihout any error, but I cannot get the
> > repot wizard to sum the column. There is a nested if statement in my
> > query.
> > I am not sure what to do. I am very new to Access.
> >
> > IIf(fxsen.buy_CCY="USD",(IIf(fxsen.sell_ccy="EUR" Or
> > fxsen.sell_ccy="GBP",(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy*fxsen.settle_rate,(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy/fxsen.settle_rate)),(IIf(fxsen.buy_ccy="EUR"
> > Or
> > fxsen.buy="GBP",(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell*fxsen.settle_rate,(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell/fxsen.settle_rate)))
> > AS Gain_Loss
> >
> > Thanks for your help.
> > Regards,
> > John
> >
>
>
>

Duane and all,

I just had an idea. The query is returning the results I want so why not
dump the results into a table. Rather the table is permanent or temporary,
and then run the report from this table. What do you think? Does Access
support temporary tables? I would just use SQL syntax to create the table,
and drop it if it is not temporary.

Apologies for all the questions, but I don't know of a good Access resource.
Any ideas about a resource would be appreciated.

Best,
John

"spartanmba" wrote:

> Duane,
>
> I tried wrapping the Iif() like you said in the query, but I still get the
> same error message from the report. Thanks for your help.
>
> John
>
> "Duane Hookom" wrote:
>
> > You might try wrap the entire IIf(.....) in Val( IIf(.....) )
> >
> > --
> > Duane Hookom
> > MS Access MVP
> >
> >
> > "spartanmba" <(E-Mail Removed)> wrote in message
> > news:097C40F9-1AFE-4A71-9407-(E-Mail Removed)...
> > > Hello,
> > >
> > > I am using report wizard to build a report from a query. I receive an
> > > error
> > > when I have the report wizard sum the gain_loss column of my query. The
> > > syntax for the gain_loss column is below. The error reads, "The
> > > expression
> > > is typed incorrectly, or it is too complex to be evaluated. For example,
> > > a
> > > numeric expression may contain too many complicated elements. Try
> > > simplifying the expression by assinging parts of the expression to
> > > variables." I can run the query wtihout any error, but I cannot get the
> > > repot wizard to sum the column. There is a nested if statement in my
> > > query.
> > > I am not sure what to do. I am very new to Access.
> > >
> > > IIf(fxsen.buy_CCY="USD",(IIf(fxsen.sell_ccy="EUR" Or
> > > fxsen.sell_ccy="GBP",(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy*fxsen.settle_rate,(fxsen.settle_rate-fxsen.contract_rate)*fxsen.buy/fxsen.settle_rate)),(IIf(fxsen.buy_ccy="EUR"
> > > Or
> > > fxsen.buy="GBP",(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell*fxsen.settle_rate,(fxsen.contract_rate-fxsen.settle_rate)*-fxsen.sell/fxsen.settle_rate)))
> > > AS Gain_Loss
> > >
> > > Thanks for your help.
> > > Regards,
> > > John
> > >
> >
> >
> >

"spartanmba" wrote

> Rather the table is permanent or temporary,
> and then run the report from this table. What
> do you think? Does Access support
> temporary tables?

Not in the sense that SQL Server does, but you can use a Make Table Query to
create a new table. The best way to do that, so you don't suffer database
bloat, is to create the temporary table inside a temporary database... when
you are done with it you can use the Kill statement to delete the temporary
database. There is an example at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP