Nz Function and Field Properties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

I've used the Nz Function in an Access 2000 query, but I cannot change the format of the output. For example, one field outputs currency containing a £ sign and ',' to separate thousands, and another field is a percentage that I require to be formatted to 2 decimal places. Please can you tell me how I can change the formatting within a field where the Nz Function has been used

Many thanks
Amanda
 
I think your issue is that Nz() returns a variant data type. Wrap your
expression in Val( ).

--
Duane Hookom
MS Access MVP


Amanda K said:
Hi All,

I've used the Nz Function in an Access 2000 query, but I cannot change the
Click to expand...
format of the output. For example, one field outputs currency containing a
£ sign and ',' to separate thousands, and another field is a percentage that
I require to be formatted to 2 decimal places. Please can you tell me how I
can change the formatting within a field where the Nz Function has been
used?
 
You have to do it from the Query Design grid. Open your query in Design
View, click in the field that uses the NZ function, then click the
Properties button or choose Properties from the View menu. Set the Format
property to what you need. The Query Designer often doesn't recognize a
field containing an expression as numeric on the first go -- so it won't
show you the Decimal Places property. Change the Format to Currency or
Standard and save and close the query. When you reopen it, the Properties
window should show the Decimal Places property for fields that you have set
to a numeric format.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/johnviescas
Amanda K said:
Hi All,

I've used the Nz Function in an Access 2000 query, but I cannot change the
Click to expand...
format of the output. For example, one field outputs currency containing a
£ sign and ',' to separate thousands, and another field is a percentage that
I require to be formatted to 2 decimal places. Please can you tell me how I
can change the formatting within a field where the Nz Function has been
used?
 
Back
Top