Change true in 'X'

[sven2]

Hello,

in a table on an SQL server I have a fielf "afwezighied" that is true or false. Now I want to display an 'X' when it is true.

I tried it like this:

strSQL = " SELECT Cursusdeelnemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, "
strSQL = strSQL & " Cursusdeelnemers.Afwezig = CASE [Afwezig] WHEN 'true' THEN 'X' ELSE '' END, "
strSQL = strSQL & " Cursusdeelnemers.[Details Afwezigheid] "
strSQL = strSQL & " FROM Cursusdeelnemers INNER JOIN Werknemers ON Cursusdeelnemers.Personeelsnummer = Werknemers.Personeelsnummer "

But this doesn't work. Can somebody tell me what I am doing wrong?

Thanks in advance,
Sven.

 

[rainman89]

Are you trying to change the value in the table? Or just show it in the query?

 

[pbaldy]

It would not be 'true', which is a text value. It would have a value of 1, so try

CASE [Afwezig] WHEN 1 THEN 'X' ELSE '' END

I'm not sure the SQL syntax is right, but I guess try the above first.

 

[sven2]

Hello,

I am not trying to change the value, I just want to display an X on the screen instead of true.

Also when I use this syntax

CASE [Afwezig] WHEN 1 THEN 'X' ELSE '' END

it doesn't work ... there is no error but the listbox is always empty.

Sven.

 

[rainman89]

try

CASE [Afwezig] WHEN 0 THEN 'X' ELSE '' END

 

[pbaldy]

Try replacing the whole second line with

strSQL = strSQL & " CASE WHEN [Afwezig] = 1 THEN 'X' ELSE '' END AS AfwezigDisplay, "

 

[sven2]

Hello,

I don't understand why but it is working like this:

Afwezig = CASE [Afwezig] WHEN 1 THEN 'X' ELSE '' END

So the strange thing is when I use

table.field = case [field] when 1 THEN 'X' ELSE '' END --> it doesn't work

or

table.field = case [table.field] when 1 THEN 'X' ELSE '' END --> it doesn't work

When I use:

field = case [field] when 1 THEN 'X' ELSE '' END --> it is ok

Very strange ...

Sven.