Can I make a number field show as yes/no in a listbox?

[Guest]

I have a table with 6 yes/no fields that I set up to be 0 or -1 to prevent
against null responses in. However, now I want to use a multi-column listbox
to show these, and my listbox isn't very user-friendly with all those 0's and
-1's. Is there any way to format these as text in the listbox?

TIA!

 

[Eric]

Hi,
If your DBMS is SQL Server you can try to modify in this way your query that
fill the listbox (I suppose that the boolean column was named "isTrue"):

Select CASE WHEN isTrue = 0 THEN 'FALSE' ELSE 'TRUE' END AS ColumnName

Bye,

Eric.

 

[Guest]

I attempted to use this and came up with the following for my listbox source
query:

SELECT tblPdC1.PdName AS [Table Name], tblPdB4.AptName AS APT, CASE WHEN
[CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE' END AS Current
FROM (tblPdB4INNER JOIN tblPdB1 ON tblPdB4.AId = tblPdB1.AId) INNER JOIN
tblPdC1 ON tblPdB4.AId = tblPdC1.AId

When I press save on the query, I get syntax error (missing operator) in
query expression 'CASE WHEN [CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE END'.

I've tried tweaking this a few times but am not sure how to fix it...

 

[Steve]

This should do it:
SELECT tblPdC1.PdName AS [Table Name], tblPdB4.AptName AS APT,
IIF([CurrentYN] = 0,"Yes","No") As TextYN
FROM (tblPdB4INNER JOIN tblPdB1 ON tblPdB4.AId = tblPdB1.AId) INNER JOIN
tblPdC1 ON tblPdB4.AId = tblPdC1.AId

Note: the assumption is that CurrentYN is 0 or -1 in ALL records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I attempted to use this and came up with the following for my listbox
source
query:

SELECT tblPdC1.PdName AS [Table Name], tblPdB4.AptName AS APT, CASE WHEN
[CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE' END AS Current
FROM (tblPdB4INNER JOIN tblPdB1 ON tblPdB4.AId = tblPdB1.AId) INNER JOIN
tblPdC1 ON tblPdB4.AId = tblPdC1.AId

When I press save on the query, I get syntax error (missing operator) in
query expression 'CASE WHEN [CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE END'.

I've tried tweaking this a few times but am not sure how to fix it...

Hi,
If your DBMS is SQL Server you can try to modify in this way your query
that
fill the listbox (I suppose that the boolean column was named "isTrue"):

Select CASE WHEN isTrue = 0 THEN 'FALSE' ELSE 'TRUE' END AS ColumnName

Bye,

Eric.

 

[Guest]

Yes, that's beautiful, thank you so much!!!

This should do it:
SELECT tblPdC1.PdName AS [Table Name], tblPdB4.AptName AS APT,
IIF([CurrentYN] = 0,"Yes","No") As TextYN
FROM (tblPdB4INNER JOIN tblPdB1 ON tblPdB4.AId = tblPdB1.AId) INNER JOIN
tblPdC1 ON tblPdB4.AId = tblPdC1.AId

Note: the assumption is that CurrentYN is 0 or -1 in ALL records.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I attempted to use this and came up with the following for my listbox
source
query:

SELECT tblPdC1.PdName AS [Table Name], tblPdB4.AptName AS APT, CASE WHEN
[CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE' END AS Current
FROM (tblPdB4INNER JOIN tblPdB1 ON tblPdB4.AId = tblPdB1.AId) INNER JOIN
tblPdC1 ON tblPdB4.AId = tblPdC1.AId

When I press save on the query, I get syntax error (missing operator) in
query expression 'CASE WHEN [CurrentYN] = 0 THEN 'FALSE' ELSE 'TRUE END'.

I've tried tweaking this a few times but am not sure how to fix it...

Hi,
If your DBMS is SQL Server you can try to modify in this way your query
that
fill the listbox (I suppose that the boolean column was named "isTrue"):

Select CASE WHEN isTrue = 0 THEN 'FALSE' ELSE 'TRUE' END AS ColumnName

Bye,

Eric.

"Ann in CA" <[email protected]> ha scritto nel messaggio
I have a table with 6 yes/no fields that I set up to be 0 or -1 to
prevent
against null responses in. However, now I want to use a multi-column
listbox
to show these, and my listbox isn't very user-friendly with all those
0's
and
-1's. Is there any way to format these as text in the listbox?

TIA!