Format question

[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.

 

[John Spencer]

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 M]

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

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

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.

 

[De Jager]

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.

 

[joelgeraldine]

v$vbùùù*ù*ù ùù*ù**