Field Format

D

doco

I am using the Format(...)function in a query like:

Ratio: Format([value]/[sale],"Fixed") where 'value' and 'sale' are fields
resident in the table being queried.

The question is I wish the results in the AS 'Ratio' field to be numeric
and not text. How is this done?


TIA
doco
 
D

doco

BTW

I have tried cdbl(format([value]/[sale],"##.#0")) which gives a numeric
result but I wish to retain the nearest hundredth (##>##) result or two
decimal places to no avail.

doco
 
D

Douglas J. Steele

I don't believe it can be done. Format always changes numeric values to
text. If you've got a recent enough version of Access, you could try using
the Round function. You could also try using Currency datatypes.
 
A

Allen Browne

Use Round() to round to the nearest cent.

Try:
IIf([sale]=0, 0, CCur(Nz(Round([value]/[sale],2),0)))

Access 97 and earlier do not have the Round() function. For an alternative,
see:
http://www.mvps.org/access/modules/mdl0054.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

doco said:
BTW

I have tried cdbl(format([value]/[sale],"##.#0")) which gives a numeric
result but I wish to retain the nearest hundredth (##>##) result or two
decimal places to no avail.

doco


doco said:
I am using the Format(...)function in a query like:

Ratio: Format([value]/[sale],"Fixed") where 'value' and 'sale' are fields
resident in the table being queried.

The question is I wish the results in the AS 'Ratio' field to be numeric
and not text. How is this done?
Click to expand...
Click to expand...
 
D

doco

CDbl(Format([value]/[sale],"#.#0")) gives numeric return and all results not
ending in '0' are two decimal places. Which will get me close enough to
what I want I suppose. Currency of course gives precisely what I want with
exception of that dollar sign...

Round(results,2) gave me the same results as with CDbl(...). Interestingly,
if CSgl(Format(...)) is used the number of decimal points is determined by
the first zero... However, using CDbl(Format([value]/[sale],"#.#0")) at
least converts any #ERROR results to 0.

Thanks
doco



Douglas J. Steele said:
I don't believe it can be done. Format always changes numeric values to
text. If you've got a recent enough version of Access, you could try using
the Round function. You could also try using Currency datatypes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



doco said:
I am using the Format(...)function in a query like:

Ratio: Format([value]/[sale],"Fixed") where 'value' and 'sale' are fields
resident in the table being queried.

The question is I wish the results in the AS 'Ratio' field to be numeric
and not text. How is this done?


TIA
doco
Click to expand...
Click to expand...
 
Top