Listbox displaying 0 and -1 instead of Yes/No

[EManning]

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.

[EManning]

On Apr 21, 10:10*am, EManning <[e-mail]:

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.

[Rich P]

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 ***

[EManning]

On Apr 21, 1:27*pm, Rich P <[e-mail]:

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 Developersdexhttp://www.developersdex.com***

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

[Salad]

EManning wrote:

On Apr 21, 10:10 am, EManning <[e-mail]:

>>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

[Bob Quintal]

EManning <ma**********@hotmail.comwrote in
news:bb1c6656-2de1-43db-83e5-79e138da9438
@u36g2000prf.googlegroups.co
m:

On Apr 21, 10:10*am, EManning <[e-mail]:

>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 **

[EManning]

On Apr 21, 4:00*pm, Salad <[e-mail]:

EManning wrote:

On Apr 21, 10:10 am, EManning <[e-mail]:

>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.

Freehttp://www.youtube.com/watch?v=hoGXKU5XM9I- 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.

[EManning]

On Apr 21, 4:43*pm, Bob Quintal <[e-mail]:

EManning <[e-mail] in
news:bb1c6656-2de1-43db-83e5-79e138da9438
@u36g2000prf.googlegroups.co
m:

On Apr 21, 10:10*am, EManning <[e-mail]:

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 fromhttp://www.teranews.com**

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