formatting text to number in query

[Guest]

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.

 

[Tom Ellison]

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

 

[John Spencer]

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))

 

[fredg]

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.

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'