By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,062 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

A2003 problem with boolean showing as -1 in list

P: n/a
Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.

Is there any fix to this?
--
Jeff
"More Access Stuff."
http://www.asken.com.au
Apr 9 '08 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no******@asken.com.au>
wrote:

Yes; it's usually a matter of formatting, or of converting the values
in the underlying query.

select iif(SomeBooleanField=0,'Nah', 'Yep')
from SomeTable

-Tom.

>Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.

Is there any fix to this?
Apr 9 '08 #2

P: n/a
So it ignores the format setting for the field. Isn't that just helpful.
Thanks MS.

Jeff

"Tom van Stiphout" <no*************@cox.netwrote in message
news:dn********************************@4ax.com...
On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no******@asken.com.au>
wrote:

Yes; it's usually a matter of formatting, or of converting the values
in the underlying query.

select iif(SomeBooleanField=0,'Nah', 'Yep')
from SomeTable

-Tom.

>>Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.

Is there any fix to this?

Apr 9 '08 #3

P: n/a
On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP" <no******@asken.com.au>
wrote:

Actually, at least in A2007, that's exactly how it works.
I just wanted to give you some workarounds.

-Tom.

>So it ignores the format setting for the field. Isn't that just helpful.
Thanks MS.

Jeff

"Tom van Stiphout" <no*************@cox.netwrote in message
news:dn********************************@4ax.com.. .
>On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no******@asken.com.au>
wrote:

Yes; it's usually a matter of formatting, or of converting the values
in the underlying query.

select iif(SomeBooleanField=0,'Nah', 'Yep')
from SomeTable

-Tom.

>>>Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.

Is there any fix to this?
Apr 9 '08 #4

P: n/a
JeffP wrote:
So it ignores the format setting for the field. Isn't that just
helpful. Thanks MS.
A format *property* is always applied to the one level you set it at. It
does not propagate. As far as I know this has always been the case. It is
no different than expecting the font color you set in a query to be
inherited by a report that uses the query. Just doesn't work that way.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Apr 9 '08 #5

P: n/a
"JeffP" <no******@asken.com.auwrote in news:47fc374b$0$13243$5a62ac22
@per-qv1-newsreader-01.iinet.net.au:
So it ignores the format setting for the field. Isn't that just helpful.
Thanks MS.

Jeff
There are format settings for a field? How quaint!
Apr 9 '08 #6

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:LS****************@nlpi061.nbdc.sbc.com:
JeffP wrote:
>So it ignores the format setting for the field. Isn't that just
helpful. Thanks MS.

A format *property* is always applied to the one level you set it at.
It does not propagate. As far as I know this has always been the
case. It is no different than expecting the font color you set in a
query to be inherited by a report that uses the query. Just doesn't
work that way.
I'm learning a lot in this group.

You can set font color in a query? I never knew that. Wow!

Another question: Why would anyone want to set font color in a query?

Would anyone actually do it?

Would these be the same people shave with rusty axes?

(Sorry, that's three questions.)
Apr 9 '08 #7

P: n/a
On Wed, 09 Apr 2008 12:52:02 +1000, JeffP wrote:
Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.
Is there any fix to this?
That is the nature of boolean, falsies are always a zero, and the truth
always seems negative.

As for a fix, manually set the values in the list box. If all you are
using for is Yes/No or True/False then enter the text and give it an
integer value of 1 or 0. You need not bind it to the database.
If you bind it to the database, then you should change your field from
boolean (yes/no) to integer. (You could use Character or Byte but that
causes problems with casting)

If you want a 0 or 1 return, have:
value=ListBoxName.Value * -1
(or is it ListBoxName * -1, I guess that depends on the version of VB and
Access)

---
Harry Skelton
sk***********@gmail.com
Jun 27 '08 #8

P: n/a
This is a list box, not a checkbox, used for displaying data from a table.
The users have already entered the data on a form using a checkbox.

The listbox is just being used to display a set of data from that table. All
I want the list box to do is show either 'Yes' or 'No' for that column which
is from a boolean field, just as they used to do in A2000.

"Harry Skelton" <sk***********@gmail.comwrote in message
news:48***********************@read.cnntp.org...
On Wed, 09 Apr 2008 12:52:02 +1000, JeffP wrote:
>Just have a small problem with boolean fields in a list box showing as 0
and -1, not Yes/No or True/False.
Is there any fix to this?

That is the nature of boolean, falsies are always a zero, and the truth
always seems negative.

As for a fix, manually set the values in the list box. If all you are
using for is Yes/No or True/False then enter the text and give it an
integer value of 1 or 0. You need not bind it to the database.
If you bind it to the database, then you should change your field from
boolean (yes/no) to integer. (You could use Character or Byte but that
causes problems with casting)

If you want a 0 or 1 return, have:
value=ListBoxName.Value * -1
(or is it ListBoxName * -1, I guess that depends on the version of VB and
Access)

---
Harry Skelton
sk***********@gmail.com

Jun 27 '08 #9

P: n/a
Hi David

I have used 2000 for years and in 2000 a boolean field displayed in a list
box always displayed as 'Yes' or 'No' when the field format was set that
way.

Just don't understand why have they changed it so it now ignores the
specified format and displays the raw data as in 0 or -1.

The reason this is disappointing is that I converted a 2000 database to 2003
and users are saying, quite rightly, what does -1 mean. They are used to the
more friendly Yes/No which they saw in 2000. So I now have to go through all
areas where there are list boxes and see where I have to change the query or
sql statement behind them to include a Format statement to show Yes or No.

Are you saying that dates are the same as I haven't noticed them yet?

Jeff

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@64.209.0 .89...
"JeffP" <no******@asken.com.auwrote in
news:47***********************@per-qv1-newsreader-01.iinet.net.au:
>This means that in all listboxes in the app I have to visit the
sql and use the Format function. I wondered where else this
approach happens?

Aargh.

Do you know if this is a bug or by design?

You think listboxes are loopy in Access? You should try them in any
other environment. You should be happy with all the help you get.

Put another way: this is a remnant of the long evolution of the
Access controls from early Visual Basic controls. There are similar
issues with formatting dates, for instance.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Jun 27 '08 #10

P: n/a
So how come in 2000 for years it has worked?

Jeff

"Rick Brandt" <ri*********@hotmail.comwrote in message
news:LS****************@nlpi061.nbdc.sbc.com...
JeffP wrote:
>So it ignores the format setting for the field. Isn't that just
helpful. Thanks MS.

A format *property* is always applied to the one level you set it at. It
does not propagate. As far as I know this has always been the case. It
is no different than expecting the font color you set in a query to be
inherited by a report that uses the query. Just doesn't work that way.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Jun 27 '08 #11

P: n/a
You have to ask?

Seems to me to be necessary, but I am not a guru.

How else would a listbox know how to display data from a field if there was
no format property?

How else could you control the formatting in a list box?

Unless you use the Format function in the sql statement for every field,
which seems to be what I need to do now in 2003 after using 2000 all these
years without having to do that?

Talk about stepping backward.
"lyle fairfield" <ly******@yah00.cawrote in message
news:Xn************************@216.221.81.119...
"JeffP" <no******@asken.com.auwrote in news:47fc374b$0$13243$5a62ac22
@per-qv1-newsreader-01.iinet.net.au:
>So it ignores the format setting for the field. Isn't that just helpful.
Thanks MS.

Jeff

There are format settings for a field? How quaint!

Jun 27 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.