Thanks for the replies...
The table has many fields, but the only one that is necessary for this
solution is called LASTNAME.
The field is built from a form where a user selects multiple last names of
people in a list box. Once the user selects the names, a SAVE button then
updates the field with the selected last names in the format of NAME, NAME,
NAME, NAME..... The names are separated by commas, except the last name in
the field where it just ends with a blank.
Reports and other forms list the names in that order.
The design may be flawed in the sense that names are stored in a single
field, but its something that has worked well. Until, someone changes their
last name and we have to then globally edit the entire table to edit the
name.
A form is used to do this, however, if the person's name changes from ROSSI
to SMITH and there is no other characters that equal to ROSSI, then we have
a successful name change to SMITH. The problem is, using INSTR in a query,
if there is someone named ROSSINI, then its still updates it SMITHNI, which
of course is incorrect.
I have been playing around with the query and the INSTR, adding a ", " to
the check and it does indeed pick up "ROSSI, ", but not if ROSSI is the only
one in the field, or the last name in the field when no commas are present.
I then added an OR to the INSTR, hoping that that I can run the query with
two INSTR's, one checking for the "ROSSI, " (comma) and one checking for
"ROSSI " (space). The query returns no results, as if the OR is not being
handled like I thought it would.
Here is the SQL from the query.
SELECT tblTurnoverLog.EntryDate, tblTurnoverLog.JCN, tblTurnoverLog.WES,
tblTurnoverLog.TechNames, tblTurnoverLog.DiscType, tblTurnoverLog.ID,
tblTurnoverLog.ShopName
FROM tblTurnoverLog
WHERE (((tblTurnoverLog.DiscType)<3) AND
((tblTurnoverLog.ShopName)=[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;
Hope that helps. Thanks again.
Chuck
KARL DEWEY said:
I got the impression that he had stored multiple data in the field
separated
by spaces.
--
KARL DEWEY
Build a little - Test a little
Jason Lepack said:
Try entering Jon followed by a space -- Jones does not have a space
after the
'n.'
However, neither does 'Jon' <g>
Chuck,
Please post an example of your table as well as the SQL of your query
so that we can help you.
Why are you using instr? Could you not just use:
SELECT lastname
FROM [your table name]
WHERE lastname = [value selected in the list box]
More detail is required before help can be given. Explain what you
are trying to do, not what you are doing. There may be a better
solution.
Cheers,
Jason Lepack
--
KARL DEWEY
Build a little - Test a little
:
I use an existing database that allows multiple selections of names
from a
list box. The field in the record is a text field and is built using
the
selection of names.
The field will have information such as:
Jones, Smith, Hilliary, Howard
I use a query that searches for a name an presents a list of records
that
have that name in the field. So, if I search for "Jones", I get the
record
with that name. The query is using the INSTR in it to find the name.
Unfortunately, after some testing, I find that if the search is for
"Jon",
it pulls up all the records with "Jon" and "Jones" in it because of
the
INSTR command.
Is there a way to search for the exact text instead of partial?
Would the
LIKE command work better?
I was thinking of changing the way the text is stored and having the
last
name have an ending comma as well, this way the search can be for
"Jones,"
and it wouldn't find "Jon," then.
Any help would be greatly appreciated. Thanks.
