formatting text to number in query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 14 Mar 2006 18:08 GMT

Good afternoon,

Please could you let me know how to format the following as a number field?
VALUE: IIf([VALUATION] Is Null,"0.00",[VALUATION])

When I now try and perform a calculation on this field it does not recognise
the 0.00 as numeric.

Reply to this Message

Tom Ellison - 14 Mar 2006 18:45 GMT

Dear Dan:

If you put "0.00" in quotes, then it isn't a number, it is a "string". Take
the double quotes off and see if that fixes this.

Tom Ellison

> Good afternoon,
>
[quoted text clipped - 5 lines]
> recognise
> the 0.00 as numeric.

Reply to this Message

John Spencer - 14 Mar 2006 19:05 GMT

VALUE: IIf([VALUATION] Is Null,0,[VALUATION])

OR

NZ(Valuation,0)
With NZ there are times when Access decides to make this a string, so you
can force the datatype with
CDbl(NZ(Valuation,0))

> Good afternoon,
>
[quoted text clipped - 5 lines]
> recognise
> the 0.00 as numeric.

Reply to this Message

fredg - 14 Mar 2006 19:43 GMT

> Good afternoon,
>
[quoted text clipped - 3 lines]
> When I now try and perform a calculation on this field it does not recognise
> the 0.00 as numeric.

Try:
IIf(IsNull([Valuation]),0,[Valuation])
If you want to actually show 0.00 then also set the control's format
property to:
#,##0.00;-#,##0.00;0.00;

I would also change the column heading to something else.
Value is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message