Expression Problem

  • Thread starter Thread starter Drew
  • Start date Start date
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!
 
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!
.
Click to expand...
.
Click to expand...
 
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?
 
Back
Top