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
On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
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?
"JeffP"
news:47fc2f4e$0$13254$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> 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
The column must be formatted before the listbox fetches it's data. So if
your listbox's rowsource is set to the name of a query, format the column in
the query designer.
So it ignores the format setting for the field. Isn't that just helpful.
Thanks MS.
Jeff
"Tom van Stiphout"
news:dncov35k34pmdkjncb3pfgftu7i9nph33f@4ax.com...
> On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
> 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?
On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP"
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"
>news:dncov35k34pmdkjncb3pfgftu7i9nph33f@4ax.com...
>> On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
>> 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?
>
I actually saved the sql statement in the listbox as a query and tried to
format the column but it made no difference. I assume you mean right-click
on the field in builder and set the format property, not using the Format
function.
Jeff
"Stuart McCall"
news:fthcl7$6of$1$8300dec7@news.demon.co.uk...
> "JeffP"
> news:47fc2f4e$0$13254$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>> 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
>
> The column must be formatted before the listbox fetches it's data. So if
> your listbox's rowsource is set to the name of a query, format the column
> in the query designer.
>
>
Hi Tom
But ignoring the format setting for the field means you have to use the
Format function in the sql statement. Isn't that a bit loopy? One would
expect that the format setting for a field is exactly what a query or
control should be using when displaying that field.
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?
Jeff
"Tom van Stiphout"
news:npeov3d3ejjr9858qiu3kefb92eoo6khsk@4ax.com...
> On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP"
> 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"
>>news:dncov35k34pmdkjncb3pfgftu7i9nph33f@4ax.com...
>>> On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
>>> 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?
>>
"JeffP"
news:47fc3eeb$0$13253$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>I actually saved the sql statement in the listbox as a query and tried to
>format the column but it made no difference. I assume you mean right-click
>on the field in builder and set the format property, not using the Format
>function.
Correct. Works for me (just tested in A2003) ...
I did this, setting the format and verified it was set to Yes/No but still
shows as -1/0. Can this be a Vista thing?
Jeff
"Stuart McCall"
news:fthg1q$8nb$1$8300dec7@news.demon.co.uk...
> "JeffP"
> news:47fc3eeb$0$13253$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>>I actually saved the sql statement in the listbox as a query and tried to
>>format the column but it made no difference. I assume you mean right-click
>>on the field in builder and set the format property, not using the Format
>>function.
>
> Correct. Works for me (just tested in A2003) ...
>
>
On Wed, 9 Apr 2008 14:04:36 +1000, "JeffP"
wrote:
As I said, it works like you desire in A2007. Perhaps you want to
take up your complaint with the boys and girls in Redmond.
-Tom.
>Hi Tom
>
>But ignoring the format setting for the field means you have to use the
>Format function in the sql statement. Isn't that a bit loopy? One would
>expect that the format setting for a field is exactly what a query or
>control should be using when displaying that field.
>
>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?
>
>Jeff
>
>"Tom van Stiphout"
>news:npeov3d3ejjr9858qiu3kefb92eoo6khsk@4ax.com...
>> On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP"
>> 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"
>>>news:dncov35k34pmdkjncb3pfgftu7i9nph33f@4ax.com...
>>>> On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
>>>> 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?
>>>
>
Sorry Tom, not meaning to get on your back at all.
Just my frustration showing through.
Looks like I will have to use the Format function in 2003. I am not a happy
chap as there are a lot of lists that show boolean fields and it is a lot of
changes, but that's my problem.
Thanks for your comments.
Jeff
"Tom van Stiphout"
news:52jov3tsq5qlvdfn11samebfc17repjecv@4ax.com...
> On Wed, 9 Apr 2008 14:04:36 +1000, "JeffP"
> wrote:
>
> As I said, it works like you desire in A2007. Perhaps you want to
> take up your complaint with the boys and girls in Redmond.
> -Tom.
>
>
>>Hi Tom
>>
>>But ignoring the format setting for the field means you have to use the
>>Format function in the sql statement. Isn't that a bit loopy? One would
>>expect that the format setting for a field is exactly what a query or
>>control should be using when displaying that field.
>>
>>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?
>>
>>Jeff
>>
>>"Tom van Stiphout"
>>news:npeov3d3ejjr9858qiu3kefb92eoo6khsk@4ax.com...
>>> On Wed, 9 Apr 2008 13:26:06 +1000, "JeffP"
>>> 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"
>>>>news:dncov35k34pmdkjncb3pfgftu7i9nph33f@4ax.com...
>>>>> On Wed, 9 Apr 2008 12:52:02 +1000, "JeffP"
>>>>> 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?
>>>>
>>
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"
@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!
"Rick Brandt"
news:LS1Lj.2328$GE1.489@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.)
lyle fairfield wrote:
> "Rick Brandt"
> news:LS1Lj.2328$GE1.489@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.
That's a good thing right? When one thinks they know all there is to know
then surely they know very little.
> You can set font color in a query? I never knew that. Wow!
Well you can set the font color of the entire datasheet and the format
property has always supported coloring of individual numeric or currency
fields.
> Another question: Why would anyone want to set font color in a query?
Why did MS add the option? Someone might find that showing negative values
in a different color is useful or if looking at two similar queries they
might find that making one have a different colored font makes it easier to
distinguish them. For that matter they might just prefer blue characters
over black.
Different people have different ideas about things. I believe that's a good
thing. That on some occassions an idea is a bad one is a natural
consequence of that, but I'm willing to allow for the occassional bad idea
over everyone having the same idea.
> Would anyone actually do it?
I'm certain at least one person would.
> Would these be the same people shave with rusty axes?
If you mean that both actions are bad ideas, I suppose some would hold that
opinion. I think just about all would agree that there is a difference of
degree between the two though.
> (Sorry, that's three questions.)
I wasn't counting.
The point I was making is that formatting (of any kind) in Access is about
the presentation of the thing you are currently looking at. There should be
no expectation that those settings are passed on to other objects.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
"JeffP"
news:47fc4055$0$13253$5a62ac22@per-qv1-newsreader-01.iinet.n et.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/
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
skelton.harry@gmail.com
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"
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=ListBoxName.Value * -1
> (or is it ListBoxName * -1, I guess that depends on the version of VB and
> Access)
>
> ---
> Harry Skelton
> skelton.harry@gmail.com
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"
news:Xns9A7BE3EE37EADf99a49ed1d0c49c5bbb2@64.209.0.89...
> "JeffP"
> news:47fc4055$0$13253$5a62ac22@per-qv1-newsreader-01.iinet.n et.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/
So how come in 2000 for years it has worked?
Jeff
"Rick Brandt"
news:LS1Lj.2328$GE1.489@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
>
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"
news:Xns9A7B4EF55D9296666646261@216.221.81.119...
> "JeffP"
> @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!