Expression Problem

D

Drew

I'm maintaining a database that I didn't create. Here's
the problem. I have a query with 3 expressions.

Expr1: IIf([FieldA]="Order Numbers",[FieldB]*-0.02,
[FieldC]*0.02)

Expr2: IIf(Status Not In ("CANC1","CANC2","CANC3"),
[FieldB],IIf([Status_date]>[End_Date],[FieldB],0))

Expr3: Expr1 + Expr 2

Expr1 is created with 4 decimal places. I looked in the
Oracle DB and found that the cost fields are defined with
25 decimal places (why, is anyone's guess). I have not
found any actual costs with more than 2 decimal places.
I have tried to add FormatNumber and FormatNumber with
out any success.

Among the silliest things that have resulted are this.

Expr1: $30.44
Expr2: $1500.20
Expr3: $1500.20$30.44

Somehow it is getting treated like a string. I've never
seen this happen in Access before. Anyone have any ideas?

Thanks!
 
D

Drew

Thanks. That works for the query (I had put in a
property of currency and that worked too). The bigger
problem, I am now discovering is that the total field on
the report is Sum(Expr3) with currency format. I'm not
sure why it's rounding since it's currency. There should
be .22 and I'm getting .23.

-----Original Message-----
Try
Expr3: CCur(Expr1) + CCur(Expr 2)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I'm maintaining a database that I didn't create. Here's
the problem. I have a query with 3 expressions.

Expr1: IIf([FieldA]="Order Numbers",[FieldB]*-0.02,
[FieldC]*0.02)

Expr2: IIf(Status Not In ("CANC1","CANC2","CANC3"),
[FieldB],IIf([Status_date]>[End_Date],[FieldB],0))

Expr3: Expr1 + Expr 2

Expr1 is created with 4 decimal places. I looked in the
Oracle DB and found that the cost fields are defined with
25 decimal places (why, is anyone's guess). I have not
found any actual costs with more than 2 decimal places.
I have tried to add FormatNumber and FormatNumber with
out any success.

Among the silliest things that have resulted are this.

Expr1: $30.44
Expr2: $1500.20
Expr3: $1500.20$30.44

Somehow it is getting treated like a string. I've never
seen this happen in Access before. Anyone have any ideas?

Thanks!
.
.
 
G

Guest

Okay there are 2 queries nearly identical. They both
work in producing 2 decimal places for the expressions.
The problem is occurring when the union query combines
those 2 queries...it creates 4 decimal places even though
the individual queries don't.

Any clue?
 

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

Top