Solved How to display a Yes/No field's value as custom text?

[mamradzelvy]

Hi,
I would like to modify the queries output value from "true;false" to a custom text, it currently outputs in my language something a direct translation would be "truth/not-truth" which is very unpleasant and disinformative for the users.
Is there any way to modify this, or maybe directly manually adjust the table/db properties to output custom values by default?

 

[Ranman256]

Make a table with your values,
0, bad
-1, good

Join this table to the query to translate the values.

 

[mamradzelvy]

Make a table with your values,
0, bad
-1, good
Join this table to the query to translate the values.

Hello, thank you for the reply, however, would you mind providing just a simple example? I'm not quite sure i understand this.
My query is a rowsource for a listbox and it's a singular line, how exactly would i make it fetch said data from a second table?

 

[Gasman]

Use an IIF() function?

SELECT Table1.*, IIf([ID]>2,"Good","Bad") AS Expr1
FROM Table1;

 

[Deleted member 147267]

To me the obvious question is, "are you working in tables and queries?", because you should not. Working in queries might not be frowned upon as much as working in tables is, but IMO they're basically the same thing - a domain. What you describe is another reason for working in forms. Your control can have any label that you want to give it. That seems the obvious reason for using a form, but it is by no means the only one.

 

[mamradzelvy]

To me the obvious question is, "are you working in tables and queries?", because you should not. Working in queries might not be frowned upon as much as working in tables is, but IMO they're basically the same thing - a domain. What you describe is another reason for working in forms. Your control can have any label that you want to give it. That seems the obvious reason for using a form, but it is by no means the only one.

HI,
you got me confused with your question.
I use a query to get a listbox rowsource based on my client name selection.
Is that something you would have frown upon? and if yes, then why?

 

[The_Doc_Man]

OK, here is the thrust of the problem: NORMALLY you would leave the values as a raw Boolean True/False field and just translate them in some formatted control on a form or report because normally nobody sees raw records. They see them through media that allows time for translation. The exception is that your list box and combo box show you "raw" content even though you are going through a form.

Gasman's solution might do the job but you have to realize that you don't see the underlying value any more. If you ever want to pick up the T/F value from one of the columns of the box, you can't see it any more unless you repeat it - once "translated" via Gasman's method, once raw in case you need to pick it up programmatically. The same problem applies to Ranman's method of translation.

Therefore, here is a question to be considered: Will there be a moment when your program needs to get the raw T/F value from that combo/list box for further operations? What you want can certainly be done but we don't want to limit your actions in subsequent programming by hiding something you need.

You also asked what Ranman's solution might resemble. This is a bit of overkill but... you might have a table of two records and two fields each record. This TFTranslation table might look like:

-1, "this is what you call a TRUE result"
0, "this is what you call a FALSE result"

Then your combo box query would be

SELECT A.yourPK, A.TFValue, B.TFName FROM yourTable AS A INNER JOIN TFTranslation AS B ON A.TFValue = B.TFValue ;

Then you would play with column widths in the combo/list box so that the raw TFValue isn't visible but the tranlated value IS visible. You could then define whatever you wanted to be visible in the box.

 

[mamradzelvy]

OK, here is the thrust of the problem: NORMALLY you would leave the values as a raw Boolean True/False field.....

Thank you very much for your feedback!

 

[Deleted member 147267]

I use a query to get a listbox rowsource based on my client name selection.

You didn't say that in your post - only that you wanted to modify what was displayed in a query.
I suppose what I'd try is adding the field to the query twice; the 2nd time calculated as in Gasman's suggestion but hide the combo column that holds the natural state of the value. I'm assuming the actual Y/N or T/F is the bound column so that might work. As a user, I'd see Good/Bad (or whatever you choose) but the value being used would be the proper one. I'd try that before adding a 1 or 2 record table just for this.