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,
tblFreightBill.DateBillEntered," _
& " format(tblFreightBill.FAKAmt,
'$#,##0.00;($#,##0.00);""";""" ' _
& " tblFreightBill.POKey " _
& " FROM tblFreightCo " _
& " INNER JOIN tblFreightBill " _
& " ON tblFreightCo.FreightCOKey = tblFreightBill.FreightCoKey
" _
& " WHERE tblFreightBill.POKey= " & lngPOKey & ";" 5 3855
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, tblFreightBill.DateBillEntered," _ & " format(tblFreightBill.FAKAmt, '$#,##0.00;($#,##0.00);""";""" ' _ & " tblFreightBill.POKey " _ & " FROM tblFreightCo " _ & " INNER JOIN tblFreightBill " _ & " ON tblFreightCo.FreightCOKey = 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,NULL,FAKAmt)
NULL will display as a blank.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRKLjC4echKqOuFEgEQIw5wCffv9QI4aTSD8V9w/m+xV5T9KJxAYAoOc8
UCF/96Pq7c0YGR+vq7FLIIBe
=Mu6E
-----END PGP SIGNATURE-----
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
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, tblFreightBill.DateBillEntered," _ & " format(tblFreightBill.FAKAmt, '$#,##0.00;($#,##0.00);""";""" ' _ & " tblFreightBill.POKey " _ & " FROM tblFreightCo " _ & " INNER JOIN tblFreightBill " _ & " ON tblFreightCo.FreightCOKey = 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,NULL,FAKAmt)
NULL will display as a blank. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
-----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv
iQA/AwUBRKLjC4echKqOuFEgEQIw5wCffv9QI4aTSD8V9w/m+xV5T9KJxAYAoOc8 UCF/96Pq7c0YGR+vq7FLIIBe =Mu6E -----END PGP SIGNATURE-----
-----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(tblFreightBill.FAKAmt=0,NULL,FAKAmt) As Amt" & _
" tblFreightBill.POKey " _
.. . .
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRKLtmIechKqOuFEgEQLK+QCghFhAURchk4mFqia1vUmqjr U/MKwAoM6z
21Nr80YDawgP+Jm13OD5gj8A
=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, tblFreightBill.DateBillEntered," _ & " format(tblFreightBill.FAKAmt, '$#,##0.00;($#,##0.00);""";""" ' _ & " tblFreightBill.POKey " _ & " FROM tblFreightCo " _ & " INNER JOIN tblFreightBill " _ & " ON tblFreightCo.FreightCOKey = 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,NULL,FAKAmt)
NULL will display as a blank. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
-----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv
iQA/AwUBRKLjC4echKqOuFEgEQIw5wCffv9QI4aTSD8V9w/m+xV5T9KJxAYAoOc8 UCF/96Pq7c0YGR+vq7FLIIBe =Mu6E -----END PGP SIGNATURE-----
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(tblFreightBill.FAKAmt=0,NULL,Format(FAKAmt,'$
#,###.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(tblFreightBill.FAKAmt=0,NULL,FAKAmt) As Amt" & _ " tblFreightBill.POKey " _
. . .
-- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
-----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv
iQA/AwUBRKLtmIechKqOuFEgEQLK+QCghFhAURchk4mFqia1vUmqjr U/MKwAoM6z 21Nr80YDawgP+Jm13OD5gj8A =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, tblFreightBill.DateBillEntered," _ & " format(tblFreightBill.FAKAmt, '$#,##0.00;($#,##0.00);""";""" ' _ & " tblFreightBill.POKey " _ & " FROM tblFreightCo " _ & " INNER JOIN tblFreightBill " _ & " ON tblFreightCo.FreightCOKey = 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,NULL,FAKAmt)
NULL will display as a blank. -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA)
-----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv
iQA/AwUBRKLjC4echKqOuFEgEQIw5wCffv9QI4aTSD8V9w/m+xV5T9KJxAYAoOc8 UCF/96Pq7c0YGR+vq7FLIIBe =Mu6E -----END PGP SIGNATURE-----
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Sky Sigal |
last post by:
(PS: Cross post from microsoft.pulic.dotnet.framework.aspnet.webcontrols)
I've been looking lately for a way to keep the Properties panel for Controls
'clean'...
My goal is to keep similar...
|
by: Shimon Sim |
last post by:
I have a custom composite control
I have following property
|
by: Lori |
last post by:
I created an install for my program. During installation,
3 User Interface dialogs display asking for values that
will be stored in the registry. I'm not registering
anything, just storing values...
|
by: Charles Law |
last post by:
I have a string similar to the following:
" MyString 40 "Hello world" all "
It contains white space that may be spaces or tabs, or a combination, and I
want to produce an array...
|
by: desi.american |
last post by:
I have a dynamically generates ASPX page with tables and data.
Depending on user selection, the same page can be viewed as a simple
web page (rendered in HTML) or as an excel spreadsheet.
If the...
|
by: Radu |
last post by:
Hi. I have created a service which I needed to install. Therefore I use
InstallUtil.
On my dev machine at home I login as Administrator and I have *NO*
password set.
In my first attempts with...
|
by: steve |
last post by:
Hi all,
Here's some work in progress that should allow you to run a batch file as a
custom action in a VS deployment project. Yup I know you can use js or wsh,
but the target may not have...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I...
|
by: hyperpau |
last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |