Format question

  • Thread starter Thread starter John M
  • Start date Start date
J

John M

I need to use the Format function in a calculated field in a non-saved
query. The table field in the calculation [intSomeVal], is an integer.
I'm having trouble figuring out how to get the zeroes and nulls to
display as non-zero ("").
Ordinarily, this format property would work
#;-#;"";""

But in a Format function I can't seem to figure out how to
get a zeroes and nulls to display as blank.
Val(Format(intSomeVal,"#;-#;?;?"))

Thanks for any suggestions.
 
Did you try using the format of
"#,-#,,"

Of course if you then apply Val to that then you are going to get zero for
zero and nulls.

So you might need to use an expression like the following instead:
IIF(IntSomeVal <> 0,IntSomeVal,Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
John,
Thanks a lot.
Pretty sure I'll use the IIF, since I do need to deliver
a numeric value.
But, I know I can use the format in other places. Never thought
of using a blank for the zero and null formatting.
Thanks for your help,
John

John Spencer said:
Did you try using the format of
"#,-#,,"

Of course if you then apply Val to that then you are going to get zero for
zero and nulls.

So you might need to use an expression like the following instead:
IIF(IntSomeVal <> 0,IntSomeVal,Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

John said:
I need to use the Format function in a calculated field in a non-saved
query. The table field in the calculation [intSomeVal], is an integer.
I'm having trouble figuring out how to get the zeroes and nulls to
display as non-zero ("").
Ordinarily, this format property would work
#;-#;"";""

But in a Format function I can't seem to figure out how to
get a zeroes and nulls to display as blank.
Val(Format(intSomeVal,"#;-#;?;?"))

Thanks for any suggestions.
Click to expand...
Click to expand...
 
John M said:
I need to use the Format function in a calculated field in a non-saved
query. The table field in the calculation [intSomeVal], is an integer.
I'm having trouble figuring out how to get the zeroes and nulls to
display as non-zero ("").
Ordinarily, this format property would work
#;-#;"";""

But in a Format function I can't seem to figure out how to
get a zeroes and nulls to display as blank.
Val(Format(intSomeVal,"#;-#;?;?"))

Thanks for any suggestions.
Click to expand...
 
Back
Top