Value isn't valid for text field

[Guest]

Hi,

I started getting the following error in one of my subforms when even
clicked on it.

"The value you entered isn't valid for this field" I went into the table to
see if I could figure out which field was producing the error. It turned out
to be a text field I created called prefix which I had set up to pull from
another table. The list is as follows:
Mr.
Ms.
Mrs.
Dr.
etc...
When I took off the period off of each it then, like this...
Mr
Ms
Mrs
Dr
etc...
There was no more error. Is there a reason a text field cannot have a
period in the data? Should I have used a different datatype or made this a
value list instead of a seperate table? I did so, so my users could add to
the list without allowing access to any of the programming areas.

 

[Guest]

How did you pull from another field? The reason I ask is that a period ( . )
can mean things in code.

Also do you have an input mask or validation set up for the field?

 

[Guest]

I went into Lookup on the under field property and selected combo box and in
row source:

SELECT [tblContactTitles].[ContactTitleID],
[tblContactTitles].[ContactTitleName] FROM tblContactTitles ORDER BY
[tblContactTitles].[ContactTitleName];

 

[Guest]

Sorry, copied the wrong field code.

SELECT tblCourtesy.CourtesyID, tblCourtesy.Prefix FROM tblCourtesy;

instead of what I just posted...sorry.

No I don't have an input mask or validation set up...should I?

 

[John Spencer]

Normally that message means that you don't have the selected value in the
referenced table.

If you have Mr and NOT Mr. (note the period), you will get the message if
you try to enter Mr. Check your reference table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

Sorry, copied the wrong field code.

SELECT tblCourtesy.CourtesyID, tblCourtesy.Prefix FROM tblCourtesy;

instead of what I just posted...sorry.

No I don't have an input mask or validation set up...should I?

Then you're trying to store the numeric CourtesyID - NOT the text prefix.

This is yet another example of why many of us hate the Lookup Field
misfeature. Your table has a *NUMERIC* field for the Courtesy value; that fact
is concealed from your view by the Lookup combo box. What you see is "Mr.";
what the computer sees is 1 or some other number.

John W. Vinson [MVP]

 

[Guest]

I changed the reference table and do not get the message anymore. So this is
all I needed to do?