Listbox displaying 0 and -1 instead of Yes/No

I'm using A2003 connected to a SQL 2000 backend. This is not an adp.
I have a table which I store 0 and -1 for 2 bit fields. I have a
listbox on a form based on a query of this table. The listbox is
supposed to display Yes or No and has sucessfully for the last 2
years. Today the user noticed that it's now displaying 0's and -1's.
If I run the query, the query displays Yes or No. Any idea on what is
happening here?

Thanks for any help or advice.

Re: Listbox displaying 0 and -1 instead of Yes/No

On Apr 21, 10:10=A0am, EManning wrote:
> I'm using A2003 connected to a SQL 2000 backend. =A0This is not an adp.
> I have a table which I store 0 and -1 for 2 bit fields. =A0I have a
> listbox on a form based on a query of this table. =A0The listbox is
> supposed to display Yes or No and has sucessfully for the last 2
> years. =A0Today the user noticed that it's now displaying 0's and -1's.
> If I run the query, the query displays Yes or No. Any idea on what is
> happening here?
>
> Thanks for any help or advice.

Update - I imported the table so that it is a pure Access table. But
it made no difference to the listbox display.

Re: Listbox displaying 0 and -1 instead of Yes/No

Hello,

Try this: create a new form and add a new listbox to it and set your
query as the recordsource for the new listbox in the new form and see
what it displays. If this new listbox still displays 1's, 0's then try
the following:

Create a DAO recordset object and set its source to your query. Then
loop through the recordset in debug mode and see what the recordset
displays

Dim RS As DAO.Recordset
Set RS = CurrentDB.OpenRecordset("Select * From yourQuery)
Do While Not RS.EOF
Debug.print RS!YourYesNofield
RS.MoveNext
Loop

If the recordset displays yes and no then the only other thing I could
suggest is to write a function that explicitly returns yes or no. YOu
can set this function in the query in the yes/no field

Public Function Yes_No(i As Integer) As String
If i = 1 Then Yes_No = "Yes"
If i = 0 Then Yes_No = "No"
End Function

Create a new column in your query and set its source like this

NewColumn=Yes_No(theOldColumn)

And now reference this column in the Listbox.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Listbox displaying 0 and -1 instead of Yes/No

On Apr 21, 1:27=A0pm, Rich P wrote:
> Hello,
>
> Try this: =A0create a new form and add a new listbox to it and set your
> query as the recordsource for the new listbox in the new form and see
> what it displays. =A0If this new listbox still displays 1's, 0's then try
> the following:
>
> Create a DAO recordset object and set its source to your query. =A0Then
> loop through the recordset in debug mode and see what the recordset
> displays
>
> Dim RS As DAO.Recordset
> Set RS =3D CurrentDB.OpenRecordset("Select * From yourQuery)
> Do While Not RS.EOF
> =A0 Debug.print RS!YourYesNofield
> =A0 RS.MoveNext
> Loop
>
> If the recordset displays yes and no then the only other thing I could
> suggest is to write a function that explicitly returns yes or no. =A0YOu
> can set this function in the query in the yes/no field
>
> Public Function Yes_No(i As Integer) As String
> =A0 If i =3D 1 Then Yes_No =3D "Yes"
> =A0 If i =3D 0 Then Yes_No =3D "No"
> End Function
>
> Create a new column in your query and set its source like this
>
> NewColumn=3DYes_No(theOldColumn)
>
> And now reference this column in the Listbox.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

I used your function idea and that worked great! Thanks for your
reply.

Re: Listbox displaying 0 and -1 instead of Yes/No

EManning wrote:

> On Apr 21, 10:10 am, EManning wrote:
>
>>I'm using A2003 connected to a SQL 2000 backend. This is not an adp.
>>I have a table which I store 0 and -1 for 2 bit fields. I have a
>>listbox on a form based on a query of this table. The listbox is
>>supposed to display Yes or No and has sucessfully for the last 2
>>years. Today the user noticed that it's now displaying 0's and -1's.
>>If I run the query, the query displays Yes or No. Any idea on what is
>>happening here?
>>
>>Thanks for any help or advice.
>
>
> Update - I imported the table so that it is a pure Access table. But
> it made no difference to the listbox display.
>
It's odd that it didn't work on an Access table. I created a table with
a Yes/No field. I set the Format in the table's Format property field
to Yes/No. I created a form and it displayed Yes/No in the listbox. I
changed the format in the table to True/False and then opened the form
and it displayed True/False. It seems to follow the format I set in the
table.

Free
http://www.youtube.com/watch?v=hoGXKU5XM9I

Re: Listbox displaying 0 and -1 instead of Yes/No

EManning wrote in
news:bb1c6656-2de1-43db-83e5-79e138da9438
@u36g2000prf.googlegroups.co
m:

> On Apr 21, 10:10 am, EManning wrote:
>> I'm using A2003 connected to a SQL 2000 backend.  This is not an
>> adp. I have a table which I store 0 and -1 for 2 bit fields.  I
>> have a listbox on a form based on a query of this table.  The
>> listbox is supposed to display Yes or No and has sucessfully for
>> the last 2 years.  Today the user noticed that it's now
>> displaying 0's and -1's. If I run the query, the query displays
>> Yes or No. Any idea on what is happening here?
>>
>> Thanks for any help or advice.
>
> Update - I imported the table so that it is a pure Access table.
> But it made no difference to the listbox display.
>
MS Office Service Pack 3 is the culprit.
http://support.microsoft.com/kb/945674

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Re: Listbox displaying 0 and -1 instead of Yes/No

On Apr 21, 4:00=A0pm, Salad wrote:
> EManning wrote:
> > On Apr 21, 10:10 am, EManning wrote:
>
> >>I'm using A2003 connected to a SQL 2000 backend. =A0This is not an adp.
> >>I have a table which I store 0 and -1 for 2 bit fields. =A0I have a
> >>listbox on a form based on a query of this table. =A0The listbox is
> >>supposed to display Yes or No and has sucessfully for the last 2
> >>years. =A0Today the user noticed that it's now displaying 0's and -1's.
> >>If I run the query, the query displays Yes or No. Any idea on what is
> >>happening here?
>
> >>Thanks for any help or advice.
>
> > Update - I imported the table so that it is a pure Access table. =A0But
> > it made no difference to the listbox display.
>
> It's odd that it didn't work on an Access table. =A0I created a table with=

> a Yes/No field. =A0I set the Format in the table's Format property field
> to Yes/No. =A0I created a form and it displayed Yes/No in the listbox. =A0=
I
> changed the format in the table to True/False and then opened the form
> and it displayed True/False. =A0It seems to follow the format I set in the=

> table.
>
> Freehttp://www.youtube.com/watch?v=3DhoGXKU5XM9I- Hide quoted text -
>
> - Show quoted text -

I guess in all my years as an Access developer I have never had a yes/
no field in a listbox before because I've never seen this behavior.
But I tried it yesterday in some other databases I've developed and
the listbox always displayed 0's and 1's instead of Yes/No. Now I
have to go back thru all of the db's I've developed and check for this
problem.

Re: Listbox displaying 0 and -1 instead of Yes/No

On Apr 21, 4:43=A0pm, Bob Quintal wrote:
> EManning wrote in
> news:bb1c6656-2de1-43db-83e5-79e138da9438
> @u36g2000prf.googlegroups.co
> m:
>
> > On Apr 21, 10:10=A0am, EManning wrote:
> >> I'm using A2003 connected to a SQL 2000 backend. =A0This is not an
> >> adp. I have a table which I store 0 and -1 for 2 bit fields. =A0I
> >> have a listbox on a form based on a query of this table. =A0The
> >> listbox is supposed to display Yes or No and has sucessfully for
> >> the last 2 years. =A0Today the user noticed that it's now
> >> displaying 0's and -1's. If I run the query, the query displays
> >> Yes or No. Any idea on what is happening here?
>
> >> Thanks for any help or advice.
>
> > Update - I imported the table so that it is a pure Access table.
> > But it made no difference to the listbox display.
>
> MS Office Service Pack 3 is the culprit.http://support.microsoft.com/kb/94=
5674
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
> ** Posted fromhttp://www.teranews.com**

Thanks for your reply. I'll check this out.