Using Report Wizard to build report from Query

[Guest]

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 Hookom]

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

 

[Guest]

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

 

[Guest]

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

 

[Larry Linson]

"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