Why is my text stored as numeric?

[shep]

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive, and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID], [tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[BruceM]

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the combo box
properties I expect you will find that the column count is 2, the bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there one
and only one record for each patient? If so, is Status a single field in
the record reflecting current status only, or is past status (and maybe
dates) in there somewhere? If Status is a single field to show current
status only, I would think you could just store the word rather than linking
to another table.

 

[shep]

Thanks for responding
tblStatusV1 is to enable using a combo box on frmPatientV1 so users can
select status vice typing.

The status fields on both tables are linked.

Column properties are as you stated they should be.

I need a query to count the number of Active patients. When I set criterion
to "Active", I get zero count. If I set it to "1", I get number of Active
patients because the ID number is store in tblPatientV1 vice the text Active.

I'm sure I hve done something to cause this, but have not foun the culprit.

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the combo box
properties I expect you will find that the column count is 2, the bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there one
and only one record for each patient? If so, is Status a single field in
the record reflecting current status only, or is past status (and maybe
dates) in there somewhere? If Status is a single field to show current
status only, I would think you could just store the word rather than linking
to another table.

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[Douglas J Steele]

You used a Lookup Field, didn't you?

It's because of confusion like this that many of us abhor lookup fields (see
http://www.mvps.org/access/lookupfields.htm for more details)

You can't get the text to show in the table, nor should you. Instead, create
a query that joins the two tables together, and work with the query wherever
you would otherwise have worked with the table.

 

[BruceM]

You haven't answered some of my questions (although I will admit I omitted a
question mark in the first one). In particular, 1) are there relationships,
and 2) is the patient record either Active, Inactive, or Discharged, or are
these three choices used to show a history? In other words, is there one
and only one record for each patient? If so, edit the combo box Row Source
so that it has only one column (the text) and just store the text value in
tblPatientV1. If you do this, make sure that the combo box column count is
1 and that there is only one column width listed (and that is above 0").
These values can be found in the combo box property sheet.
Douglas Steele also has weighed in on this. His suggestion is well worth
your attention, especially if you are getting nowhere with mine. No matter
to whom you respond, it is best if you answer the questions. They are
relevant to the problem, and the answers may be necessary before a targeted
suggestion can be offered.

Thanks for responding
tblStatusV1 is to enable using a combo box on frmPatientV1 so users can
select status vice typing.

The status fields on both tables are linked.

Column properties are as you stated they should be.

I need a query to count the number of Active patients. When I set
criterion
to "Active", I get zero count. If I set it to "1", I get number of Active
patients because the ID number is store in tblPatientV1 vice the text
Active.

I'm sure I hve done something to cause this, but have not foun the
culprit.

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the combo
box
properties I expect you will find that the column count is 2, the bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on
the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there
one
and only one record for each patient? If so, is Status a single field in
the record reflecting current status only, or is past status (and maybe
dates) in there somewhere? If Status is a single field to show current
status only, I would think you could just store the word rather than
linking
to another table.

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active,
Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in
tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[shep]

I did make that mistake - not knowing better. Status in tblPatientV1 was
initially set as a lookup field to tblStatusV1.

After I learned that lookup fields in tables are not good to have, I changed
it to a text field. On the form I made it a combo box with row source
tblStatusV1.

When I link the two tables in a query, I draw a blank.

I am quite confused now, so I have to research some more and try to sort it
out.

You used a Lookup Field, didn't you?

It's because of confusion like this that many of us abhor lookup fields (see
http://www.mvps.org/access/lookupfields.htm for more details)

You can't get the text to show in the table, nor should you. Instead, create
a query that joins the two tables together, and work with the query wherever
you would otherwise have worked with the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive, and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID], [tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[shep]

You are right, and I apologize. I thought I answered the question about
relationships when I stated that the tables were linked (joined) with the
Status field in each table.

I failed to answer teh question about records. The status is to show
current status, not history.

I made the changes you suggested and that does produce the result I wanted.

However, I and also trying to follow Mr Steele's advice. I have changed
fields I initially set to lookup fields to text fields in tblPatientV1, and
use combo boxes on the form. I have not sorted out the procedure he
suggested regarding joining the tables in a query. But I will pursue this
course since I have read other counseling against lookup fields in tables.

Again, I apologize for not responding fully to all questions; it was not
intentional.

And I appreciate your assistance.

You haven't answered some of my questions (although I will admit I omitted a
question mark in the first one). In particular, 1) are there relationships,
and 2) is the patient record either Active, Inactive, or Discharged, or are
these three choices used to show a history? In other words, is there one
and only one record for each patient? If so, edit the combo box Row Source
so that it has only one column (the text) and just store the text value in
tblPatientV1. If you do this, make sure that the combo box column count is
1 and that there is only one column width listed (and that is above 0").
These values can be found in the combo box property sheet.
Douglas Steele also has weighed in on this. His suggestion is well worth
your attention, especially if you are getting nowhere with mine. No matter
to whom you respond, it is best if you answer the questions. They are
relevant to the problem, and the answers may be necessary before a targeted
suggestion can be offered.

Thanks for responding
tblStatusV1 is to enable using a combo box on frmPatientV1 so users can
select status vice typing.

The status fields on both tables are linked.

Column properties are as you stated they should be.

I need a query to count the number of Active patients. When I set
criterion
to "Active", I get zero count. If I set it to "1", I get number of Active
patients because the ID number is store in tblPatientV1 vice the text
Active.

I'm sure I hve done something to cause this, but have not foun the
culprit.

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the combo
box
properties I expect you will find that the column count is 2, the bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on
the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there
one
and only one record for each patient? If so, is Status a single field in
the record reflecting current status only, or is past status (and maybe
dates) in there somewhere? If Status is a single field to show current
status only, I would think you could just store the word rather than
linking
to another table.

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active,
Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in
tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[BruceM]

I wasn't trying to scold you, I was trying to help you streamline the
process of finding answers. The thing I had not understood was whether ID
in tblStatusV1 was the primary key for that table or the foreign key (linked
field) with tblPatientV1.
Douglas Steele has provided many helpful answers in this forum. He
understood right away that you were using lookup fields, and in general his
knowledge and experience surpass my own by a considerable margin. I would
suggest that you pursue this matter in that portion of the thread. I
appreciate hearing back from you so that I know my ideas were received.
Finding out about what doesn't work is part of my own learning process.
Good luck.

You are right, and I apologize. I thought I answered the question about
relationships when I stated that the tables were linked (joined) with the
Status field in each table.

I failed to answer teh question about records. The status is to show
current status, not history.

I made the changes you suggested and that does produce the result I
wanted.

However, I and also trying to follow Mr Steele's advice. I have changed
fields I initially set to lookup fields to text fields in tblPatientV1,
and
use combo boxes on the form. I have not sorted out the procedure he
suggested regarding joining the tables in a query. But I will pursue this
course since I have read other counseling against lookup fields in tables.

Again, I apologize for not responding fully to all questions; it was not
intentional.

And I appreciate your assistance.

You haven't answered some of my questions (although I will admit I
omitted a
question mark in the first one). In particular, 1) are there
relationships,
and 2) is the patient record either Active, Inactive, or Discharged, or
are
these three choices used to show a history? In other words, is there one
and only one record for each patient? If so, edit the combo box Row
Source
so that it has only one column (the text) and just store the text value
in
tblPatientV1. If you do this, make sure that the combo box column count
is
1 and that there is only one column width listed (and that is above 0").
These values can be found in the combo box property sheet.
Douglas Steele also has weighed in on this. His suggestion is well worth
your attention, especially if you are getting nowhere with mine. No
matter
to whom you respond, it is best if you answer the questions. They are
relevant to the problem, and the answers may be necessary before a
targeted
suggestion can be offered.

Thanks for responding
tblStatusV1 is to enable using a combo box on frmPatientV1 so users can
select status vice typing.

The status fields on both tables are linked.

Column properties are as you stated they should be.

I need a query to count the number of Active patients. When I set
criterion
to "Active", I get zero count. If I set it to "1", I get number of
Active
patients because the ID number is store in tblPatientV1 vice the text
Active.

I'm sure I hve done something to cause this, but have not foun the
culprit.

:

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the
combo
box
properties I expect you will find that the column count is 2, the
bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on
the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there
one
and only one record for each patient? If so, is Status a single field
in
the record reflecting current status only, or is past status (and
maybe
dates) in there somewhere? If Status is a single field to show
current
status only, I would think you could just store the word rather than
linking
to another table.

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active,
Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text
field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data
for
storage in the table. On the form, Status field is a combo box and
Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in
tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[shep]

Thanks again for your help

You used a Lookup Field, didn't you?

It's because of confusion like this that many of us abhor lookup fields (see
http://www.mvps.org/access/lookupfields.htm for more details)

You can't get the text to show in the table, nor should you. Instead, create
a query that joins the two tables together, and work with the query wherever
you would otherwise have worked with the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active, Inactive, and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data for
storage in the table. On the form, Status field is a combo box and Row
Source for Status field has:SELECT [tblStatusV1].[ID], [tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in tblPatientV1
rather than the text Active.

How can I get the text to show in the table?

 

[shep]

Thanks again for your help

I wasn't trying to scold you, I was trying to help you streamline the
process of finding answers. The thing I had not understood was whether ID
in tblStatusV1 was the primary key for that table or the foreign key (linked
field) with tblPatientV1.
Douglas Steele has provided many helpful answers in this forum. He
understood right away that you were using lookup fields, and in general his
knowledge and experience surpass my own by a considerable margin. I would
suggest that you pursue this matter in that portion of the thread. I
appreciate hearing back from you so that I know my ideas were received.
Finding out about what doesn't work is part of my own learning process.
Good luck.

You are right, and I apologize. I thought I answered the question about
relationships when I stated that the tables were linked (joined) with the
Status field in each table.

I failed to answer teh question about records. The status is to show
current status, not history.

I made the changes you suggested and that does produce the result I
wanted.

However, I and also trying to follow Mr Steele's advice. I have changed
fields I initially set to lookup fields to text fields in tblPatientV1,
and
use combo boxes on the form. I have not sorted out the procedure he
suggested regarding joining the tables in a query. But I will pursue this
course since I have read other counseling against lookup fields in tables.

Again, I apologize for not responding fully to all questions; it was not
intentional.

And I appreciate your assistance.

You haven't answered some of my questions (although I will admit I
omitted a
question mark in the first one). In particular, 1) are there
relationships,
and 2) is the patient record either Active, Inactive, or Discharged, or
are
these three choices used to show a history? In other words, is there one
and only one record for each patient? If so, edit the combo box Row
Source
so that it has only one column (the text) and just store the text value
in
tblPatientV1. If you do this, make sure that the combo box column count
is
1 and that there is only one column width listed (and that is above 0").
These values can be found in the combo box property sheet.
Douglas Steele also has weighed in on this. His suggestion is well worth
your attention, especially if you are getting nowhere with mine. No
matter
to whom you respond, it is best if you answer the questions. They are
relevant to the problem, and the answers may be necessary before a
targeted
suggestion can be offered.

Thanks for responding
tblStatusV1 is to enable using a combo box on frmPatientV1 so users can
select status vice typing.

The status fields on both tables are linked.

Column properties are as you stated they should be.

I need a query to count the number of Active patients. When I set
criterion
to "Active", I get zero count. If I set it to "1", I get number of
Active
patients because the ID number is store in tblPatientV1 vice the text
Active.

I'm sure I hve done something to cause this, but have not foun the
culprit.

:

Are there any relationships between the tables. It sounds as if ID in
tblPatientV1 is related to ID in tblStatusV1. If you look at the
combo
box
properties I expect you will find that the column count is 2, the
bound
column is 1, and the column widths are 0";1" (second number could be
different). This is as it should be. You can see what you need to on
the
form; there is no need to view the information directly in the tables.
Having said that, I wonder a bit about your database design. Is there
one
and only one record for each patient? If so, is Status a single field
in
the record reflecting current status only, or is past status (and
maybe
dates) in there somewhere? If Status is a single field to show
current
status only, I would think you could just store the word rather than
linking
to another table.

I have a table, tblPatientV1, and a table, tblStatusV1.
tblStatusV1 has two fields; ID and Status. Status has Active,
Inactive,
and
Discharged entered as options.

tblPatientV1 has several fields one of which is Status, a text
field.

form, frmPatientV1 is based on tblPatientV1 to enter patient data
for
storage in the table. On the form, Status field is a combo box and
Row
Source for Status field has:SELECT [tblStatusV1].[ID],
[tblStatusV1].[Status]
FROM [tblStatusV1];

When I select a status;e.g., Active, The ID number shows in
tblPatientV1
rather than the text Active.

How can I get the text to show in the table?