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]

Re: Custom Format - QUOTES - blank when 0

sara wrote:[color=blue]
> 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 & ";"
>[/color]

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

Re: Custom Format - QUOTES - blank when 0


MGFoster wrote:[color=blue]
> sara wrote:[color=green]
> > 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);""
> >[/color][/color]

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]

Re: Custom Format - QUOTES - blank when 0

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:[color=blue]
> sara wrote:[color=green]
> > 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 & ";"
> >[/color]
>
> -----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-----[/color]

[MGFoster]

Re: Custom Format - QUOTES - blank when 0

-----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:[color=blue]
> 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:
>[color=green]
>>sara wrote:
>>[color=darkred]
>>>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 & ";"
>>>[/color]
>>
>>-----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-----[/color]
>
>[/color]

[sara]

Re: Custom Format - QUOTES - blank when 0

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:[color=blue]
> -----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:[color=green]
> > 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:
> >[color=darkred]
> >>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-----[/color]
> >
> >[/color][/color]