decimal places

[cruiser01]

Have an issue with decimal places. Have a query with that looks like this:

Select [MasterOrder] as Orderno, Costperlb
from COSTDATA
where MasterOrder > 0
Union Select [Order2] as Orderno, Costperlb
from COSTDATA
where Order2 > 0
Union Select [Order3] as Orderno, Costperlb
from COSTDATA
where Order3 >0
UNION Select [Order4] as Orderno, Costperlb
from COSTDATA
where Order4 >0
UNION Select [Order5] as Orderno, Costperlb
from COSTDATA
where Order5 >0
UNION Select [Order6] as Orderno, Costperlb
from COSTDATA
where Order6 >0;

Unfortunately, all the results for Orderno are calcuations that bring back a varying amount of decimal places. Want to limit to 3 decimal places.

 

[hneal98]

Use the format function like this:

format([ORDERX], "####.###")

where X is the number of the order. You would do the same to MasterOrder as well.

 

[cruiser01]

Sorry, I led you astray. The calculated field is Costperlb. I previously tried:

format([Costperlb],"####.###")

But, this returns a "missing operator" error.

 

[hneal98]

So, if you do it like this, you get an error?

Select [MasterOrder] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where MasterOrder > 0
Union Select [Order2] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where Order2 > 0
Union Select [Order3] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where Order3 >0
UNION Select [Order4] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where Order4 >0
UNION Select [Order5] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where Order5 >0
UNION Select [Order6] as Orderno, Format(Costperlb, "####.##")
from COSTDATA
where Order6 >0;

 

[mtompkins]

I have a slightly similar issue - perhaps I can continue this thread.

I have multiple products

product A
product B
product C etc...

all in a products table

Each product will have a DIFFERENT decimal format so

product A ##.##
product B .####
product C ####.

I would like to make this dynamic on the form entry so that in this instance - any entry of 4 digits will allocate automatically where they belong - so if product A is chosen in the form and the "price" of 1234 is entered, it will be displayed and recorded as 12.34 - for product be, an entry of 1234 would be displayed and recorded as .1234 - any help would be appreciated.

I'm guessing a matrix of product vs format in the products table - but from there I'm lost.

Kindest,
mark

 

[cruiser01]

Interstingly enough, it does work, but somehow looses track of the fact that the field was actually called "Costperlb". Comes back as "Exp1001".

 

[cruiser01]

sorry, "Expr1001

 

[Golom]

cruiser01
On your first select use
[tt]
Select [MasterOrder] as Orderno, Format(Costperlb, "####.##") [/tt][tt]As CostPerLb[/tt]

 

[cruiser01]

It created a circular reference but that name wasn't important so I just simply changed it to "Cost". Problem solved.

Thanks to everyone.