convert yes/no values to text

G

Guest

Sorry if this has been covered before. I found a few postings, but non that
matched what I need.

I have a form with an option group for "new_client" that the user can select
yes or no.

I would like this value to be displayed in a table, in text format, rather
than the value.

What currently appears is 1 for yes, and 2 for no. I would like to see just
the text (yes or no) in the table, not the value (1 or 2).

Any suggestions would be greatly appreciated.

Alicia
 
R

Rick B

Huh?

A YES/NO field stores -1 for yes and 0 for no.

You should not tamper with what is stored in the table. If this is truly
set as a 'checkbox' or a boolean field, then it should store -1 or 0.

Since you NEVER look at the table or access it for any reason, it should not
matter what is in the table. In your forms, queries, and reports you can
control how that value displays to the users. If set up properly, they will
never know there is a -1 and a 0 in the table.
 
A

Al Camp

If you display the source table, you'll just see 0's and -1's, so create
a query with an unbound calculated field to display "Yes" or "No" according
to the value of New_Client.
In the query design grid, add a column with...
txtNewClient : IIF(New_Client = True, "Yes", "No")
hth
Al Camp
 
R

Rick B

Ah - you say it is an option group.

Not sure why you would make a yes/no field into an option group,but ht
eprincipla still holds.

The data in the table is not important.

You can cause it to display for the users in whatever format you want.
 
G

Guest

Alicia,
Take no offense, please, you can do what you want, but I don't understand
why you want to go to so much troulbe to do something that is much easier if
you design it properly.

First, Why an Option Group for only a Yes/No field? A Check Box is better
for this situation.

Next, Why do you want to see the text in the table? There is really no
reason to do that. If you are thinking of using it on a report, then you
could use something like

txtNewClient = IIf(NewClient,"Yes","No")

It also makes logic easier if you are doing queries or filters if you use a
Yes/No field instead of a text field.

And last, you will have to complicate your form to do what you want to do.

Now that I am done preaching, here is what you will need to do (if you must)

Disclaimer - If this is one of those bone head user requirements, I
sympathize.

Unbind your Option Group from the table.
Create an invisible text box on your form and bind it the your NewClient
field in your table.
In the After Update event of the Option Group:
Me.txtNewClient = Iif(Me.opgName = 1,"Yes", "No")
 
D

Dirk Goldgar

Al Camp said:
If you display the source table, you'll just see 0's and -1's, so
create a query with an unbound calculated field to display "Yes" or
"No" according to the value of New_Client.
In the query design grid, add a column with...
txtNewClient : IIF(New_Client = True, "Yes", "No")

Actually, Alicia has told us that her option group is storing 1 for yes,
2 for no. So the expression for a calculated field or control, with
that setup, would be better as

Choose([Nz(New_Client], 3), "Yes", "No", "Unknown")

Better yet would be for Alicia to change her option group's option
values to -1 for Yes and 0 for No. Then she could set the field's
Format property to "Yes/No", and it would automatically display the way
she wants.
 
G

Guest

I originally created a check box, but management wants to see options for
both yes and no, from which a user will select one. I want the text rather
than the value to display, because a few users will be working with the data
in the table and the number value won't make sense to them, but the text
will. I will try your suggestions, thanks.
 
G

Guest

I knew it! bone head user requirement. (gag)
Allowing users to work directly in tables (shudder - make frequent back ups)

Good Luck
 
A

Al Camp

Dirk,
You're right... I was reading Rick B's response when I realized that I too
had missed that point. I went right off on "boolean to text" solution.

Yes, Alicia should be using a Boolean field there, but... for her option
group, your code is right on.

I like that NZ,3 bit you added to return "UnKnown"... slick.

Al Camp

Dirk Goldgar said:
Al Camp said:
If you display the source table, you'll just see 0's and -1's, so
create a query with an unbound calculated field to display "Yes" or
"No" according to the value of New_Client.
In the query design grid, add a column with...
txtNewClient : IIF(New_Client = True, "Yes", "No")

Actually, Alicia has told us that her option group is storing 1 for yes,
2 for no. So the expression for a calculated field or control, with
that setup, would be better as

Choose([Nz(New_Client], 3), "Yes", "No", "Unknown")

Better yet would be for Alicia to change her option group's option
values to -1 for Yes and 0 for No. Then she could set the field's
Format property to "Yes/No", and it would automatically display the way
she wants.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
G

Guest

I have to agree with Klatuu on this one. Do yourself a favour (or whoever
has to maintain this application) and save yourself the pain when a user
starts messing directly in the tables.

Even at the bare minimum, wrap a minimal UI around it using the New Form
Wizard. If you really need to display the words Yes/No, that is where you
should do it.

And if worse comes to worst, set the Yes/No field in the database to a
Lookup with a value list of Yes;No. That way True is still -1 and False is 0.

Good luck.

Lance
 
G

Guest

Thanks all.

LTofsrud said:
I have to agree with Klatuu on this one. Do yourself a favour (or whoever
has to maintain this application) and save yourself the pain when a user
starts messing directly in the tables.

Even at the bare minimum, wrap a minimal UI around it using the New Form
Wizard. If you really need to display the words Yes/No, that is where you
should do it.

And if worse comes to worst, set the Yes/No field in the database to a
Lookup with a value list of Yes;No. That way True is still -1 and False is 0.

Good luck.

Lance
 
J

Joan Wild

LTofsrud said:
I have to agree with Klatuu on this one. Do yourself a favour (or whoever
has to maintain this application) and save yourself the pain when a user
starts messing directly in the tables.

And if worse comes to worst, set the Yes/No field in the database to a
Lookup with a value list of Yes;No. That way True is still -1 and False
is 0.


Or in the table, set the Display Control to textbox; and then set the format
to yes/no.
 
R

Rick B

Users should NEVER work IN THE TABLE.

You know, you can also make Yes/No fields drop-downs if they really want
both options.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top