Rounding Woes

[Eyespi20]

I am pulling a number from one database(Sql 2000) into Access for the purpose of computing sales tax. I need to round up a figure like 3.025 to 3.03, but Access is refusing to do this -- it's leaving it at 3.02 instead.

I've tried to convert the field to a more exact one than float, but Access does not like Cast or Convert commands within a select statement.

Does anyone have a solution for this issue?

Thanks!

Margaret

 

[PHV]

You may try this:
CDec(Format([yourField],"#.##"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eyespi20]

Nope, that did not do it -- made everything a zero :'(

It's not the FORMAT of the number I need to change -- I need to actually round it up in order to calculate the tax correctly.

Do you know if case statements work in Access?

Thanks!

Margaret

 

[PHV]

Nope, that did not do it
Can you please post the SQL code you tried with some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eyespi20]

Simple version :

            select round(dbo.gp.tax_pct,2)AS tax_pct
            from 
                 dbo.gp_tax_pct 
            where
                 dbo.gp_tax_pct.zip = dbo.contract.zip

if the gp_tax_pct = 3.025, it returns 3.02 not 3.03 since the datatype of gp_tax_pct in the SQL database is a decimal and I can't change it.

If I try to do

           select convert(smallmoney,(9,2), gp.tax_pct)

I get an error and the same with

         select cast (gp.tax_pct AS Smallmoney)

 

[PHV]

Is it a PassThru query ?
Is dbo.gp_tax_pct a linked table ?
Have you tried this ?
select CDec(Format(dbo.gp.tax_pct,"#.##")) AS tax_pct

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eyespi20]

No, it's not exactly a pass through as I am pulling information from two tables located in two different databases. They are linked, however.

Yes, I've tried that, but that is a FORMAT, and does not ROUND the number -- the actual number returned is unchanged from 3.025 (as an example).

 

[PHV]

This works for me (ac2003):
select 0+Format(dbo.gp.tax_pct,'#.##') AS tax_pct

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Eyespi20]

Thanks -- I've solved it by creating a view on my database and rounding there first.

Margaret