Quick Format question

[Cind]

Hello,

How do I get a string to display "N/A" when its zero (in
the table). I have tried using a IF in the details
format event, but it doens't work for some reason.
Thanks

Cind

 

[Rick Brandt]

Hello,

How do I get a string to display "N/A" when its zero (in
the table). I have tried using a IF in the details
format event, but it doens't work for some reason.

From Access 97 Help file
*******************
Custom Formats

Custom number formats can have one to four sections with semicolons ( as the list
separator. Each section contains the format specification for a different type of
number.

Section Description
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null values.

For example, you could use the following custom Currency format:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

This number format contains four sections separated by semicolons and uses a
different format for each section.
If you use multiple sections but don't specify a format for each section, entries for
which there is no format either will display nothing or will default to the
formatting of the first section.

 

[JoeElla]

If I understand you correctly. Make the data base field not visible. Lay
an unbound text field over it which has "N/A" as its value. Set its visable
property to no also. In the on format event of the detail line use this
code.
if (database field)=0 then
let (unbound text field).visible=true
let (database field).visible=false
else
let (database field).visible=true
let (unbound text field).visible=false
endif

Quote from my first msg:

Strings, not numbers.

Cind

-----Original Message-----


From Access 97 Help file
*******************
Custom Formats

Custom number formats can have one to four sections with semicolons ( as the list
separator. Each section contains the format

specification for a different type of

number.

Section Description
First The format for positive numbers.
Second The format for negative numbers.
Third The format for zero values.
Fourth The format for Null values.

For example, you could use the following custom Currency format:

$#,##0.00[Green];($#,##0.00)[Red];"Zero";"Null"

This number format contains four sections separated by semicolons and uses a
different format for each section.
If you use multiple sections but don't specify a format for each section, entries for
which there is no format either will display nothing or will default to the
formatting of the first section.


.

 

[Rick Brandt]

Quote from my first msg:


Strings, not numbers.

=IIf([YourFieldName]="0","N/A",[YourFieldName])

This won't be editable though if you use it on a form.

 

[cind]

Hi,

Sounds like it could work, but where exactly do I put
that (keeping in mind im editing this in the report
section).

-----Original Message-----

Quote from my first msg:


Strings, not numbers.

=IIf([YourFieldName]="0","N/A",[YourFieldName])

This won't be editable though if you use it on a form.


.

 

[Rick Brandt]

Hi,

Sounds like it could work, but where exactly do I put
that (keeping in mind im editing this in the report
section).

Put the expression as the ControlSource for a new TextBox that you drop on the
Report. The existing control can be removed.

 

[Cind]

doenst work, give me #error
Cind

-----Original Message-----


Put the expression as the ControlSource for a new TextBox that you drop on the
Report. The existing control can be removed.


.

 

[Rick Brandt]

doenst work, give me #error
Cind

Please post the exact ControlSource you used as well as the name and type of
control you used.