Yes/No field showing as 0 -1

  • Thread starter Thread starter Penny Miller
  • Start date Start date
P

Penny Miller

I have a table that has a yes/no field in it called "completed", when I
create a query based off of this table this field shows as a 0 or a -1. How
do I code this so it will show either Yes or No?
 
When you bind this field to a text box in your form or report, set the format
property to
Format: Yes/No
 
I am really rusty on my coding for I haven't done it in a while and I still
need to make a similar change to a different field that isn't a yes/no field.
This field is a text field called "SPO" and is 3 characters long which is
the probation officers initials.

I need it to say the name of the probation officer name instead of their
initials
SPO = LLL (Lori Lewis)
 
I am really rusty on my coding for I haven't done it in a while and I still
need to make a similar change to a different field that isn't a yes/no field.
This field is a text field called "SPO" and is 3 characters long which is
the probation officers initials.

I need it to say the name of the probation officer name instead of their
initials
SPO = LLL (Lori Lewis)
Click to expand...

Where does that information reside? Do you have an Officers table with fields
for the initials and the name? If so you can use a Query joining the tables...


John W. Vinson [MVP]
 
Include the table that contains both the name and the initials in your query.
Join the initials fields.
 
I do not have an officers table w/the names. Basically, I have inherited
this project of converting a Visual Dbase 5.0 database to Access 2007. The
"SPO" field is in the OFFENDID table w/all the other fields which doesn't
include the names of the officers. Any ideas?

What I'd like to do is create a report that allows a probation officer to
select only his or hers cases and then puts their name in the header. I have
the parameter for the query... Like "*" & [Enter Probation Officer] & "*" I
just have to figure out how to either hard code their name in or get the
names in the table some how and I'm not sure how to do this.
 
I do not have an officers table w/the names.
Click to expand...

Ummmm... then how could your database possible print out names!?
Basically, I have inherited
this project of converting a Visual Dbase 5.0 database to Access 2007. The
"SPO" field is in the OFFENDID table w/all the other fields which doesn't
include the names of the officers. Any ideas?
Click to expand...

What does the SPO field contain? Just the initials?
What I'd like to do is create a report that allows a probation officer to
select only his or hers cases and then puts their name in the header. I have
the parameter for the query... Like "*" & [Enter Probation Officer] & "*" I
just have to figure out how to either hard code their name in or get the
names in the table some how and I'm not sure how to do this.
Click to expand...

This isn't making sense. You're asking how to search the database for a name
which the database does not contain.

What I would recommend is that you create a lookup table with three fields -
SPO (three character text, primary key), FirstName, and LastName. Type in the
initials and the officers' names. Create a Query joining your OFFENDID table
to this table, joining by SPO. You'll then be able to search using the name
fields.

John W. Vinson [MVP]
 
Hi John,
Currently, the database doesn't print out their names... I'd like it to
though. The SPO field is a 3char field w/initials only and it currently
resides in the OffendID table. Originally, when this system was created in
Visual Dbase 3.0 there were no other tables but the one (OffendID) and there
were no keys generated automatically so the 5000+ records that are entered
into this system currently have no primary key.

OK, I've created a Okey (offenderID table) and I created a SPOkey (SPO table
which has the initials and names of the probation officers) as you
recommended. In the query I have the correct SPOname showing w/SPO that is
involved w/each case :) yeah!!!

Now, in the report... I have a fill in parameter "*" & [Enter Probation
Officer] & "*" now if this field is left blank I want it to show "All
Probation Officers" in the report title rather their name. How do I do that?

Thank you so much for you assistance, I really appreciate it.

John W. Vinson said:
I do not have an officers table w/the names.
Click to expand...

Ummmm... then how could your database possible print out names!?
Basically, I have inherited
this project of converting a Visual Dbase 5.0 database to Access 2007. The
"SPO" field is in the OFFENDID table w/all the other fields which doesn't
include the names of the officers. Any ideas?
Click to expand...

What does the SPO field contain? Just the initials?
What I'd like to do is create a report that allows a probation officer to
select only his or hers cases and then puts their name in the header. I have
the parameter for the query... Like "*" & [Enter Probation Officer] & "*" I
just have to figure out how to either hard code their name in or get the
names in the table some how and I'm not sure how to do this.
Click to expand...

This isn't making sense. You're asking how to search the database for a name
which the database does not contain.

What I would recommend is that you create a lookup table with three fields -
SPO (three character text, primary key), FirstName, and LastName. Type in the
initials and the officers' names. Create a Query joining your OFFENDID table
to this table, joining by SPO. You'll then be able to search using the name
fields.

John W. Vinson [MVP]
Click to expand...
 
OK, I've created a Okey (offenderID table) and I created a SPOkey (SPO table
which has the initials and names of the probation officers) as you
recommended. In the query I have the correct SPOname showing w/SPO that is
involved w/each case :) yeah!!!

Now, in the report... I have a fill in parameter "*" & [Enter Probation
Officer] & "*" now if this field is left blank I want it to show "All
Probation Officers" in the report title rather their name. How do I do that?
Click to expand...

Try setting the Control Source of a textbox to

=IIF(IsNull([Enter Probation Officer], "All Probation Officers",
[OfficerName])


John W. Vinson [MVP]
 
Ok this is what I put in the (Expression Builder) SPOName Control Source
textbox. I used [SPO] because that is the field that asks the question Enter
Probation Officer? And I used [SPOName] because that is the field from the
Query that has the complete name of the probation officer.

=IIF(IsNull([SPO], "All Probation Officers",[SPOName])

I receive an error... The expression you entered has a function containing
the wrong number of arguments.

John W. Vinson said:
OK, I've created a Okey (offenderID table) and I created a SPOkey (SPO table
which has the initials and names of the probation officers) as you
recommended. In the query I have the correct SPOname showing w/SPO that is
involved w/each case :) yeah!!!

Now, in the report... I have a fill in parameter "*" & [Enter Probation
Officer] & "*" now if this field is left blank I want it to show "All
Probation Officers" in the report title rather their name. How do I do that?
Click to expand...

Try setting the Control Source of a textbox to

=IIF(IsNull([Enter Probation Officer], "All Probation Officers",
[OfficerName])


John W. Vinson [MVP]
Click to expand...
 
I receive an error... The expression you entered has a function containing
the wrong number of arguments.
Click to expand...

Missing a parenthesis:

=IIF(IsNull([SPO]), "All Probation Officers",[SPOName])

although I *think* what you want instead of SPO is whatever prompt you're
using:

=IIF(IsNull( [Enter Probation Officer]), "All Probation Officers",[SPOName])

That way if the user makes no entry to the prompt [Enter probation officer]
the report will display the text string "All Probation Officers"; if they do
enter a name, it will display that name.

John W. Vinson [MVP]
 
John,
When I use the code =IIF(IsNull([SPO]), "All Probation Officers",[SPOName])
and if I leave the prompt blank or key in the spo's initials the report
brings up the correct information except the control field says #Error.

When I use the code =IIF(IsNull( [Enter Probation Officer]), "All Probation
Officers",[SPOName]) and if I leave the prompt blank it puts in the "All
Probation Officers" but if I key in REH (a probation officer) the title comes
back w/an #Error and not their name
Penny



John W. Vinson said:
I receive an error... The expression you entered has a function containing
the wrong number of arguments.
Click to expand...

Missing a parenthesis:

=IIF(IsNull([SPO]), "All Probation Officers",[SPOName])

although I *think* what you want instead of SPO is whatever prompt you're
using:

=IIF(IsNull( [Enter Probation Officer]), "All Probation Officers",[SPOName])

That way if the user makes no entry to the prompt [Enter probation officer]
the report will display the text string "All Probation Officers"; if they do
enter a name, it will display that name.

John W. Vinson [MVP]
Click to expand...
 
Back
Top