A2003 problem with boolean showing as -1 in list

[JeffP]

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

[Tom van Stiphout]

Re: A2003 problem with boolean showing as -1 in list

On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no-reply@asken.com .au>
wrote:

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

select iif(SomeBoolean Field=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?

[JeffP]

Re: A2003 problem with boolean showing as -1 in list

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

Jeff

"Tom van Stiphout" <no.spam.tom774 4@cox.netwrote in message
news:dncov35k34 pmdkjncb3pfgftu 7i9nph33f@4ax.c om...

On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no-reply@asken.com .au>
wrote:
>
Yes; it's usually a matter of formatting, or of converting the values
in the underlying query.
>
select iif(SomeBoolean Field=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?

[Tom van Stiphout]

Re: A2003 problem with boolean showing as -1 in list

On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP" <no-reply@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.spam.tom774 4@cox.netwrote in message
>news:dncov35k3 4pmdkjncb3pfgft u7i9nph33f@4ax. com...

>On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP" <no-reply@asken.com .au>
>wrote:
>>
>Yes; it's usually a matter of formatting, or of converting the values
>in the underlying query.
>>
>select iif(SomeBoolean Field=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?

>

[Rick Brandt]

Re: A2003 problem with boolean showing as -1 in list

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

[lyle fairfield]

Re: A2003 problem with boolean showing as -1 in list

"JeffP" <no-reply@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!

[lyle fairfield]

Re: A2003 problem with boolean showing as -1 in list

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in
news:LS1Lj.2328 $GE1.489@nlpi06 1.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.)

[Harry Skelton]

Re: A2003 problem with boolean showing as -1 in list

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=ListBoxNa me.Value * -1
(or is it ListBoxName * -1, I guess that depends on the version of VB and
Access)

---
Harry Skelton
skelton.harry@g mail.com

[JeffP]

Re: A2003 problem with boolean showing as -1 in list

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" <skelton.harry@ gmail.comwrote in message
news:4808f1c9$0 $22076$6e1ede2f @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=ListBoxNa me.Value * -1
(or is it ListBoxName * -1, I guess that depends on the version of VB and
Access)
>
---
Harry Skelton
skelton.harry@g mail.com

[JeffP]

Re: A2003 problem with boolean showing as -1 in list

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" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9A7BE3E E37EADf99a49ed1 d0c49c5bbb2@64. 209.0.89...

"JeffP" <no-reply@asken.com .auwrote in
news:47fc4055$0 $13253$5a62ac22 @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/

[JeffP]

Re: A2003 problem with boolean showing as -1 in list

So how come in 2000 for years it has worked?

Jeff

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in message
news:LS1Lj.2328 $GE1.489@nlpi06 1.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
>

[JeffP]

Re: A2003 problem with boolean showing as -1 in list

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" <lylefa1r@yah00 .cawrote in message
news:Xns9A7B4EF 55D929666664626 1@216.221.81.11 9...

"JeffP" <no-reply@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!