Search Text String For Exact Text

C

Chuck

Thanks for the replies... I am re-posting.

The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.

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.

IE: ROSSI, SMITH, JONES, CLARK

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
 
K

Ken Snell \(MVP\)

Your query shows an AND, not an OR, in the portion of the WHERE clause that
uses the InStr function....should be this:

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])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;


--

Ken Snell
<MS ACCESS MVP>



Chuck said:
Thanks for the replies... I am re-posting.

The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.

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.

IE: ROSSI, SMITH, JONES, CLARK

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
Click to expand...
 
C

chuckr1958

Your query shows an AND, not an OR, in the portion of the WHERE clause that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>




Thanks for the replies... I am re-posting.
Click to expand...
The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.
Click to expand...
The field is built from a form where a user selects multiple last namesof
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 namein
the field where it just ends with a blank.
Click to expand...
IE: ROSSI, SMITH, JONES, CLARK
Click to expand...
Reports and other forms list the names in that order.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Here is the SQL from the query.
Click to expand...
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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;
Click to expand...
Hope that helps. Thanks again.
Click to expand...
Chuck- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
 
K

Ken Snell \(MVP\)

Sorry, misread the field names in those subclauses.

What exactly is the text string that is in
[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name] control? Does
it have the "," or not? Assuming that it does not:

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 OR [technames] =
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>


Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>




Thanks for the replies... I am re-posting.
Click to expand...
The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.
Click to expand...
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.
Click to expand...
IE: ROSSI, SMITH, JONES, CLARK
Click to expand...
Reports and other forms list the names in that order.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Here is the SQL from the query.
Click to expand...
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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;
Click to expand...
Hope that helps. Thanks again.
Click to expand...
Chuck- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
 
K

Ken Snell \(MVP\)

I just sent a post that didn't take into account that a single name ends
with space.


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 OR
InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]
& " ")>0))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used for
control names, field names, etc. See these Knowledge Base articles for more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>




Thanks for the replies... I am re-posting.
Click to expand...
The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.
Click to expand...
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.
Click to expand...
IE: ROSSI, SMITH, JONES, CLARK
Click to expand...
Reports and other forms list the names in that order.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Here is the SQL from the query.
Click to expand...
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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;
Click to expand...
Hope that helps. Thanks again.
Click to expand...
Chuck- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
 
C

Chuck

Thanks Ken for the quick response. However, something is still amiss. The
INSTR function seems to not like a space as part of the search criteria.

Let's use the name ROSSI.

The field can contain the text (minus the quotes) "ROSSI" or "SMITH, ROSSI"
or "SMITH, ROSSI, JONES"

So, the INSTR has to check for "ROSSI <space>" or "<comma><space> ROSSI
<space>" or "<comma><space> ROSSI <comma><space>"

I hope that makes some sense.

The problem in the first INSTR, if the name happens to be "XYZROSSI", the
record is still found. Its not an exact text of "ROSSI".

The second and third INSTR check seems to work, however, the space is not
being recognized. It's as if its ignored and no record is found.

Maybe a query is not the right thing here. Maybe a combination of checking
the name's length and then using it as criteria?

Also, thanks for the heads up on the use of NAME as a field, I'll have to
change that in the next revision.


Ken Snell (MVP) said:
I just sent a post that didn't take into account that a single name ends
with space.


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 OR
InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]
& " ")>0))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form. It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>




Thanks for the replies... I am re-posting.
Click to expand...
The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.
Click to expand...
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.
Click to expand...
IE: ROSSI, SMITH, JONES, CLARK
Click to expand...
Reports and other forms list the names in that order.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Here is the SQL from the query.
Click to expand...
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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;
Click to expand...
Hope that helps. Thanks again.
Click to expand...
Chuck- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
Click to expand...
 
K

Ken Snell \(MVP\)

You're now seeing why a relational database should store atomic data in
separate fields / records, and not concatenated into a single field. Your
query gets very complicated. If you were storing each name in a separate
record, your query would just look for the value "ROSSI" and that's it.

As I see it, there are three possibilities for how ROSSI might be stored in
a field in your database:

(1) By itself: "ROSSI" (still not sure if it has a trailing space
character, but I doubt that it does because ACCESS typically strips trailing
space characters when data are entered via a form)

(2) At the beginning of the field's string: "ROSSI, SMITH, JONES"

(3) Not at the beginning of the field's string: "SMITH, JONES,
ROSSI" or "SMITH, ROSSI, JONES"

In order to find ROSSI in all three scenarios, one needs to test for all
three conditions.

So let's try this query, which assumes no trailing space character when
"ROSSI" is all by itself:

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
([technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like ", " &
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] & ", ")
ORDER BY tblTurnoverLog.EntryDate;


--

Ken Snell
<MS ACCESS MVP>

Chuck said:
Thanks Ken for the quick response. However, something is still amiss.
The INSTR function seems to not like a space as part of the search
criteria.

Let's use the name ROSSI.

The field can contain the text (minus the quotes) "ROSSI" or "SMITH,
ROSSI" or "SMITH, ROSSI, JONES"

So, the INSTR has to check for "ROSSI <space>" or "<comma><space> ROSSI
<space>" or "<comma><space> ROSSI <comma><space>"

I hope that makes some sense.

The problem in the first INSTR, if the name happens to be "XYZROSSI", the
record is still found. Its not an exact text of "ROSSI".

The second and third INSTR check seems to work, however, the space is not
being recognized. It's as if its ignored and no record is found.

Maybe a query is not the right thing here. Maybe a combination of
checking the name's length and then using it as criteria?

Also, thanks for the heads up on the use of NAME as a field, I'll have to
change that in the next revision.


Ken Snell (MVP) said:
I just sent a post that didn't take into account that a single name ends
with space.


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 OR
InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]
& " ")>0))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form.
It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>





Thanks for the replies... I am re-posting.

The table has many fields, but the only one that is necessary for this
solution is called TECHNAMES.

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.

IE: ROSSI, SMITH, JONES, CLARK

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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;

Hope that helps. Thanks again.

Chuck- Hide quoted text -

- Show quoted text -
Click to expand...

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

By the way, another way to skin this cat would be to put a user-defined VBA
function in your database that will return a True/False value depending upon
whether it finds the desired text in the concatenated data. If you're
interested in this approach, post back and let me know -- I'll show you how
to write it.

--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
You're now seeing why a relational database should store atomic data in
separate fields / records, and not concatenated into a single field. Your
query gets very complicated. If you were storing each name in a separate
record, your query would just look for the value "ROSSI" and that's it.

As I see it, there are three possibilities for how ROSSI might be stored
in a field in your database:

(1) By itself: "ROSSI" (still not sure if it has a trailing
space character, but I doubt that it does because ACCESS typically strips
trailing space characters when data are entered via a form)

(2) At the beginning of the field's string: "ROSSI, SMITH, JONES"

(3) Not at the beginning of the field's string: "SMITH, JONES,
ROSSI" or "SMITH, ROSSI, JONES"

In order to find ROSSI in all three scenarios, one needs to test for all
three conditions.

So let's try this query, which assumes no trailing space character when
"ROSSI" is all by itself:

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
([technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like ", " &
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] & ", ")
ORDER BY tblTurnoverLog.EntryDate;


--

Ken Snell
<MS ACCESS MVP>

Chuck said:
Thanks Ken for the quick response. However, something is still amiss.
The INSTR function seems to not like a space as part of the search
criteria.

Let's use the name ROSSI.

The field can contain the text (minus the quotes) "ROSSI" or "SMITH,
ROSSI" or "SMITH, ROSSI, JONES"

So, the INSTR has to check for "ROSSI <space>" or "<comma><space> ROSSI
<space>" or "<comma><space> ROSSI <comma><space>"

I hope that makes some sense.

The problem in the first INSTR, if the name happens to be "XYZROSSI", the
record is still found. Its not an exact text of "ROSSI".

The second and third INSTR check seems to work, however, the space is not
being recognized. It's as if its ignored and no record is found.

Maybe a query is not the right thing here. Maybe a combination of
checking the name's length and then using it as criteria?

Also, thanks for the heads up on the use of NAME as a field, I'll have to
change that in the next revision.


Ken Snell (MVP) said:
I just sent a post that didn't take into account that a single name ends
with space.


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 OR
InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]
& " ")>0))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form.
It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

On Apr 28, 4:24 pm, "Ken Snell \(MVP\)"
Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>





Thanks for the replies... I am re-posting.

The table has many fields, but the only one that is necessary for
this
solution is called TECHNAMES.

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.

IE: ROSSI, SMITH, JONES, CLARK

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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;

Hope that helps. Thanks again.

Chuck- Hide quoted text -

- Show quoted text -

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

The field can contain the text (minus the quotes) "ROSSI" or "SMITH, ROSSI"
or "SMITH, ROSSI, JONES"
Click to expand...

That's the source of the problem, then.

You're storing multiple facts in one field. Fields should be "atomic" -
containing only ONE piece of information. You've essentially got a one-to-many
relationship embedded in a single field. Ideally you should have *a second
table* with one record for Smith, one record for Rossi, and one record for
Jones!

YOu can still do this with a rather snarky query testing all the possible
options:

=[Enter last name]
OR LIKE [Enter last name] & ",*"
OR LIKE "* " & [Enter last name] & ",*"
OR LIKE "* " & [Enter last name]

to find "ROSSI", "ROSSI, <other names>", "<other names>, ROSSI, <other
names>", and "other names>, ROSSI" respectively.

John W. Vinson [MVP]
 
C

Chuck

Thanks John. The LIKE function worked exactly as you coded it.

I understand the the one-to-many relationship and now realize the poor
design of it. What started out as a simple concept has turned into one big
maintenance issue.

I am not sure of an easy way to separate the field into separate tables and
then relate them to the record they are in. There would have to be some
thought into this as there are reports, queries and forms all linking to
this field.

Thanks again for the help.

John W. Vinson said:
The field can contain the text (minus the quotes) "ROSSI" or "SMITH,
ROSSI"
or "SMITH, ROSSI, JONES"
Click to expand...

That's the source of the problem, then.

You're storing multiple facts in one field. Fields should be "atomic" -
containing only ONE piece of information. You've essentially got a
one-to-many
relationship embedded in a single field. Ideally you should have *a second
table* with one record for Smith, one record for Rossi, and one record for
Jones!

YOu can still do this with a rather snarky query testing all the possible
options:

=[Enter last name]
OR LIKE [Enter last name] & ",*"
OR LIKE "* " & [Enter last name] & ",*"
OR LIKE "* " & [Enter last name]

to find "ROSSI", "ROSSI, <other names>", "<other names>, ROSSI, <other
names>", and "other names>, ROSSI" respectively.

John W. Vinson [MVP]
Click to expand...
 
K

Ken Snell \(MVP\)

Forgot my wild cards....

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
([technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like "*, " &
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] & "*" OR
[technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] & ",*")
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>

Ken Snell (MVP) said:
You're now seeing why a relational database should store atomic data in
separate fields / records, and not concatenated into a single field. Your
query gets very complicated. If you were storing each name in a separate
record, your query would just look for the value "ROSSI" and that's it.

As I see it, there are three possibilities for how ROSSI might be stored
in a field in your database:

(1) By itself: "ROSSI" (still not sure if it has a trailing
space character, but I doubt that it does because ACCESS typically strips
trailing space characters when data are entered via a form)

(2) At the beginning of the field's string: "ROSSI, SMITH, JONES"

(3) Not at the beginning of the field's string: "SMITH, JONES,
ROSSI" or "SMITH, ROSSI, JONES"

In order to find ROSSI in all three scenarios, one needs to test for all
three conditions.

So let's try this query, which assumes no trailing space character when
"ROSSI" is all by itself:

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
([technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like ", " &
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] OR
[technames] Like
[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name] & ", ")
ORDER BY tblTurnoverLog.EntryDate;


--

Ken Snell
<MS ACCESS MVP>

Chuck said:
Thanks Ken for the quick response. However, something is still amiss.
The INSTR function seems to not like a space as part of the search
criteria.

Let's use the name ROSSI.

The field can contain the text (minus the quotes) "ROSSI" or "SMITH,
ROSSI" or "SMITH, ROSSI, JONES"

So, the INSTR has to check for "ROSSI <space>" or "<comma><space> ROSSI
<space>" or "<comma><space> ROSSI <comma><space>"

I hope that makes some sense.

The problem in the first INSTR, if the name happens to be "XYZROSSI", the
record is still found. Its not an exact text of "ROSSI".

The second and third INSTR check seems to work, however, the space is not
being recognized. It's as if its ignored and no record is found.

Maybe a query is not the right thing here. Maybe a combination of
checking the name's length and then using it as criteria?

Also, thanks for the heads up on the use of NAME as a field, I'll have to
change that in the next revision.


Ken Snell (MVP) said:
I just sent a post that didn't take into account that a single name ends
with space.


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 OR
InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]![Name]
& " ")>0))
ORDER BY tblTurnoverLog.EntryDate;


Also, I note that you're using Name as the name of a control on a form.
It
and many other words are reserved words in ACCESS and should not be used
for
control names, field names, etc. See these Knowledge Base articles for
more
information about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

On Apr 28, 4:24 pm, "Ken Snell \(MVP\)"
Your query shows an AND, not an OR, in the portion of the WHERE clause
that
uses the InStr function....should be this:

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]![frmTechnicianssu­bform]![Shopname])
OR
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0)))
ORDER BY tblTurnoverLog.EntryDate;

--

Ken Snell
<MS ACCESS MVP>





Thanks for the replies... I am re-posting.

The table has many fields, but the only one that is necessary for
this
solution is called TECHNAMES.

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.

IE: ROSSI, SMITH, JONES, CLARK

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]![frmTechnicianssub­form]![Shopname])
AND
((InStr(1,[technames],[Forms]![frmTableMaintenance]![frmTechnicianssubform]­![Name]))>0))
ORDER BY tblTurnoverLog.EntryDate;

Hope that helps. Thanks again.

Chuck- Hide quoted text -

- Show quoted text -

The "AND" condition is valid. The selection critieria for for NAME
and SHOP. The problem lies in the INSTR where it finds any reference
to the string. I need to find an exact match.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I am not sure of an easy way to separate the field into separate tables and
then relate them to the record they are in. There would have to be some
thought into this as there are reports, queries and forms all linking to
this field.
Click to expand...

It can be done... and I've earned a fair number of billable hours and spent a
fair bit of skull sweat doing it. It's ALWAYS easier to do it right the first
time than it is to fix afterward (he says, looking at the warped coldframe he
built last year for his wife...). Good luck!

John W. Vinson [MVP]
 

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

Similar Threads

Searching Text String 4
help with finding lowercase characters in table 2
Search criteria for words in the "middle" of text string 6
Return string using RIGHT function 4
Using Prompt with a "like" query parameter 2
Expression to middle name/initial from "Last, First Middle" 1
Delete part of text from field 3
reformatting a name 2

Top