Custom Format - QUOTES - blank when 0

[sara]

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

But I can't, for the life of me, get it to work in the following VBA
string. I have a total field that is 0 unless there's a total. I want
the user to see BLANK if it's zero (that's how they think of it).

thanks
sara

strSQL = " SELECT tblFreightBill. FreightBillKey,
tblFreightCo.Fr eightCo, " _
& " tblFreightBill. FreightBillNum, tblFreightBill. Freight, " _
& " tblFreightBill. FreightBillDate ,
tblFreightBill. DateBillEntered ," _
& " format(tblFreig htBill.FAKAmt,
'$#,##0.00;($#, ##0.00);""";""" ' _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.Fr eightCOKey = tblFreightBill. FreightCoKey
" _
& " WHERE tblFreightBill. POKey= " & lngPOKey & ";"

[MGFoster]

sara wrote:

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

But I can't, for the life of me, get it to work in the following VBA
string. I have a total field that is 0 unless there's a total. I want
the user to see BLANK if it's zero (that's how they think of it).

thanks
sara

strSQL = " SELECT tblFreightBill. FreightBillKey,
tblFreightCo.Fr eightCo, " _
& " tblFreightBill. FreightBillNum, tblFreightBill. Freight, " _
& " tblFreightBill. FreightBillDate ,
tblFreightBill. DateBillEntered ," _
& " format(tblFreig htBill.FAKAmt,
'$#,##0.00;($#, ##0.00);""";""" ' _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.Fr eightCOKey = tblFreightBill. FreightCoKey
" _
& " WHERE tblFreightBill. POKey= " & lngPOKey & ";"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Zero is considered a positive number and, therefore, will be formatted
according to the format you gave for positive numbers.

You should leave the formatting to the display layer (forms & reports)
not the data retrieval layer (queries).

To substitute an empty string: Instead of using the Format() function
use an IIF() function in the display layer (the ControlSource property
of a control):

=IIf(FAKAmt=0,N ULL,FAKAmt)

NULL will display as a blank.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLjC4echKq OuFEgEQIw5wCffv 9QI4aTSD8V9w/m+xV5T9KJxAYAoO c8
UCF/96Pq7c0YGR+vq7F LIIBe
=Mu6E
-----END PGP SIGNATURE-----

[Clint Stowers]

MGFoster wrote:

sara wrote:

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

One of a few possible solutions to your problem may be just to set the
Control holding the value to Visible = False. Just Hide the control.

But don't forget to turn it back on again if >0

Just a thought

[sara]

Thanks for the quick response.

I wasn't clear, I guess. I am not formatting the data in a control,
but for a list box.

Should I try to find the value, set it (as you say, 0, blank, whatever
I can figure out) in a variable and then put the variable in my strSQL
for the listbox?

Overall, my users will be totally confused if they see "0" (or $0.00),
so I want to just have the column blank.

Will that even work?

Sara
MGFoster wrote:

sara wrote:

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

But I can't, for the life of me, get it to work in the following VBA
string. I have a total field that is 0 unless there's a total. I want
the user to see BLANK if it's zero (that's how they think of it).

thanks
sara

strSQL = " SELECT tblFreightBill. FreightBillKey,
tblFreightCo.Fr eightCo, " _
& " tblFreightBill. FreightBillNum, tblFreightBill. Freight, " _
& " tblFreightBill. FreightBillDate ,
tblFreightBill. DateBillEntered ," _
& " format(tblFreig htBill.FAKAmt,
'$#,##0.00;($#, ##0.00);""";""" ' _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.Fr eightCOKey = tblFreightBill. FreightCoKey
" _
& " WHERE tblFreightBill. POKey= " & lngPOKey & ";"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Zero is considered a positive number and, therefore, will be formatted
according to the format you gave for positive numbers.

You should leave the formatting to the display layer (forms & reports)
not the data retrieval layer (queries).

To substitute an empty string: Instead of using the Format() function
use an IIF() function in the display layer (the ControlSource property
of a control):

=IIf(FAKAmt=0,N ULL,FAKAmt)

NULL will display as a blank.
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLjC4echKq OuFEgEQIw5wCffv 9QI4aTSD8V9w/m+xV5T9KJxAYAoO c8
UCF/96Pq7c0YGR+vq7F LIIBe
=Mu6E
-----END PGP SIGNATURE-----

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For a ListBox RowSource you can use the IIf() function, I posted
earlier, in the query's SELECT clause:

.. . .

& " IIf(tblFreightB ill.FAKAmt=0,NU LL,FAKAmt) As Amt" & _
" tblFreightBill. POKey " _

.. . .

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLtmIechKq OuFEgEQLK+QCghF hAURchk4mFqia1v UmqjrU/MKwAoM6z
21Nr80YDawgP+Jm 13OD5gj8A
=m8h7
-----END PGP SIGNATURE-----

sara wrote:

Thanks for the quick response.

I wasn't clear, I guess. I am not formatting the data in a control,
but for a list box.

Should I try to find the value, set it (as you say, 0, blank, whatever
I can figure out) in a variable and then put the variable in my strSQL
for the listbox?

Overall, my users will be totally confused if they see "0" (or $0.00),
so I want to just have the column blank.

Will that even work?

Sara
MGFoster wrote:

sara wrote:

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

But I can't, for the life of me, get it to work in the following VBA
string. I have a total field that is 0 unless there's a total. I want
the user to see BLANK if it's zero (that's how they think of it).

thanks
sara

strSQL = " SELECT tblFreightBill. FreightBillKey,
tblFreightCo .FreightCo, " _
& " tblFreightBill. FreightBillNum, tblFreightBill. Freight, " _
& " tblFreightBill. FreightBillDate ,
tblFreightBi ll.DateBillEnte red," _
& " format(tblFreig htBill.FAKAmt,
'$#,##0.00;( $#,##0.00);"""; """ ' _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.Fr eightCOKey = tblFreightBill. FreightCoKey
" _
& " WHERE tblFreightBill. POKey= " & lngPOKey & ";"

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Zero is considered a positive number and, therefore, will be formatted
according to the format you gave for positive numbers.

You should leave the formatting to the display layer (forms & reports)
not the data retrieval layer (queries).

To substitute an empty string: Instead of using the Format() function
use an IIF() function in the display layer (the ControlSource property
of a control):

=IIf(FAKAmt=0,N ULL,FAKAmt)

NULL will display as a blank.
--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLjC4echKq OuFEgEQIw5wCffv 9QI4aTSD8V9w/m+xV5T9KJxAYAoO c8
UCF/96Pq7c0YGR+vq7F LIIBe
=Mu6E
-----END PGP SIGNATURE-----

[sara]

PHENOMENAL!! Thanks!

Here's what I did and it works! Thanks so much. I didn't know you
could embed an If statement like this. Powerful.

Sara
.....
& " tblFreightBill. FreightBillDate , tblFreightBill. DateBillEntered ," _
& " IIf(tblFreightB ill.FAKAmt=0,NU LL,Format(FAKAm t,'$
#,###.00')) As Amt, " _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
.....
MGFoster wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

For a ListBox RowSource you can use the IIf() function, I posted
earlier, in the query's SELECT clause:

. . .

& " IIf(tblFreightB ill.FAKAmt=0,NU LL,FAKAmt) As Amt" & _
" tblFreightBill. POKey " _

. . .

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLtmIechKq OuFEgEQLK+QCghF hAURchk4mFqia1v UmqjrU/MKwAoM6z
21Nr80YDawgP+Jm 13OD5gj8A
=m8h7
-----END PGP SIGNATURE-----

sara wrote:

Thanks for the quick response.

I wasn't clear, I guess. I am not formatting the data in a control,
but for a list box.

Should I try to find the value, set it (as you say, 0, blank, whatever
I can figure out) in a variable and then put the variable in my strSQL
for the listbox?

Overall, my users will be totally confused if they see "0" (or $0.00),
so I want to just have the column blank.

Will that even work?

Sara
MGFoster wrote:

sara wrote:

I've looked at all the posts I could and don't see the solution.
I am aware that the format to get a BLANK when the number is 0 is:

$ #,###.00;($ #,###.00);""

But I can't, for the life of me, get it to work in the following VBA
string. I have a total field that is 0 unless there's a total. I want
the user to see BLANK if it's zero (that's how they think of it).

thanks
sara

strSQL = " SELECT tblFreightBill. FreightBillKey,
tblFreightCo .FreightCo, " _
& " tblFreightBill. FreightBillNum, tblFreightBill. Freight, " _
& " tblFreightBill. FreightBillDate ,
tblFreightBi ll.DateBillEnte red," _
& " format(tblFreig htBill.FAKAmt,
'$#,##0.00;( $#,##0.00);"""; """ ' _
& " tblFreightBill. POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.Fr eightCOKey = tblFreightBill. FreightCoKey
" _
& " WHERE tblFreightBill. POKey= " & lngPOKey & ";"
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Zero is considered a positive number and, therefore, will be formatted
according to the format you gave for positive numbers.

You should leave the formatting to the display layer (forms & reports)
not the data retrieval layer (queries).

To substitute an empty string: Instead of using the Format() function
use an IIF() function in the display layer (the ControlSource property
of a control):

=IIf(FAKAmt=0,N ULL,FAKAmt)

NULL will display as a blank.
--
MGFoster:::mg f00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKLjC4echKq OuFEgEQIw5wCffv 9QI4aTSD8V9w/m+xV5T9KJxAYAoO c8
UCF/96Pq7c0YGR+vq7F LIIBe
=Mu6E
-----END PGP SIGNATURE-----