no field format properties

[NetworkTrade]

Access2003 In report, two fields can not be made into currency format
because their format property has nothing in the drop down.... they display
as a general number.

all the other fields are fine; their property format displays all the
choices when you look at the drop down.....

can't seem to find anything unique in the underlying source query that would
cause this - am baffled.

 

[NetworkTrade]

found the cause, and am still baffled

the field in question is: Nz([A],0)

took out the Nz and it now has format properties in the report.....never
realized that Nz would do that....

 

[Allen Browne]

This issue has to do with data types.

Nz() is a VBA function that returns a Variant. In VBA, a variant has a
subdata type (such as currency), but JET (the query engine in Access)
doesn't recognise that.

In part, that's because a VBA function could return a variant of different
subdata types in different rows of a query, whereas JET has to choose one
data type for the entire column. Therefore it makes the safest choice - Text
(or sometimes Binary.) Visually you can see JET treating it as Text as the
column is displayed left-aligned (like text), not right-aligned (like dates
or numbers.)

So, how to you avoid this?

Solution 1: Explicitly typecast the result back to currency:
CCur(Nz([A], 0))

Solution 2: Use IIf()
JET knows this function, and will examine the 2 options and determine the
data type correctly. You also use Is Null (which is part of the query
language) rather than IsNull() which is part of VBA. So:
IIf([A] Is Null, 0, [A])

The 2nd is a little more typing, but I expect it to be more efficient to
execute.

More info on data types in JET:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

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

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

found the cause, and am still baffled

the field in question is: Nz([A],0)

took out the Nz and it now has format properties in the report.....never
realized that Nz would do that....

--
NTC

Access2003 In report, two fields can not be made into currency format
because their format property has nothing in the drop down.... they
display
as a general number.

all the other fields are fine; their property format displays all the
choices when you look at the drop down.....

can't seem to find anything unique in the underlying source query that
would
cause this - am baffled.

 

[Duane Hookom]

The Nz() function returns a variant. You can wrap the expression inside Val()
like:
=Val(Nz([A],0))
--
Duane Hookom
Microsoft Access MVP

found the cause, and am still baffled

the field in question is: Nz([A],0)

took out the Nz and it now has format properties in the report.....never
realized that Nz would do that....

--
NTC

Access2003 In report, two fields can not be made into currency format
because their format property has nothing in the drop down.... they display
as a general number.

all the other fields are fine; their property format displays all the
choices when you look at the drop down.....

can't seem to find anything unique in the underlying source query that would
cause this - am baffled.

 

[NetworkTrade]

thanks both;

yep noticed the left justification (as if text) which is what clued me into
the nz being the cause

changed it to an iif and all works fine

learned something new on this one.....

--
NTC

The Nz() function returns a variant. You can wrap the expression inside Val()
like:
=Val(Nz([A],0))
--
Duane Hookom
Microsoft Access MVP

found the cause, and am still baffled

the field in question is: Nz([A],0)

took out the Nz and it now has format properties in the report.....never
realized that Nz would do that....

--
NTC

Access2003 In report, two fields can not be made into currency format
because their format property has nothing in the drop down.... they display
as a general number.

all the other fields are fine; their property format displays all the
choices when you look at the drop down.....

can't seem to find anything unique in the underlying source query that would
cause this - am baffled.