Query Interpreting Criteria as text

[Guest]

I have query using two linked tables. Table one has an agent code (unique)
and the agents first name. Table two has the agent code (not unique) and a
freeform text field. I join the tables based on the agent code and want to
search all records where the free form text field contains the agents first
name. It wants to search on the field name rather than the contents of the
field. The sql is:

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS) Like "*[mlsadmin_members]![la_first
name]*"))
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

 

[John Spencer]

You need to get the value in the field. A little concatenation, should work
for you along with testing to make sure that the
[mlsadmin_members].[la_first name] is not null.

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS)
Like "*" & [mlsadmin_members].[la_first name] & "*")) AND
[mlsadmin_members].[la_first name] is Not Null
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

 

[Guest]

That makes it want to evaluate mlsadmin_members.La_first_name as a parameter
that I would enter at run time. I want it to use the value in
Mlsadmin_members.La_first_name as the parameter to search for in
Mlsadmin_Prop_1.remarks.

You need to get the value in the field. A little concatenation, should work
for you along with testing to make sure that the
[mlsadmin_members].[la_first name] is not null.

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS)
Like "*" & [mlsadmin_members].[la_first name] & "*")) AND
[mlsadmin_members].[la_first name] is Not Null
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

I have query using two linked tables. Table one has an agent code
(unique)
and the agents first name. Table two has the agent code (not unique) and
a
freeform text field. I join the tables based on the agent code and want to
search all records where the free form text field contains the agents
first
name. It wants to search on the field name rather than the contents of
the
field. The sql is:

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS) Like "*[mlsadmin_members]![la_first
name]*"))
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

 

[Amy Blankenship]

What he means is use the & operator to concatenate rather than dumping the
name of the field in the string like that.

Like "*" & [mlsadmin_members]![la_first name] &"*"

HTH;

Amy

That makes it want to evaluate mlsadmin_members.La_first_name as a
parameter
that I would enter at run time. I want it to use the value in
Mlsadmin_members.La_first_name as the parameter to search for in
Mlsadmin_Prop_1.remarks.

You need to get the value in the field. A little concatenation, should
work
for you along with testing to make sure that the
[mlsadmin_members].[la_first name] is not null.

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS)
Like "*" & [mlsadmin_members].[la_first name] & "*")) AND
[mlsadmin_members].[la_first name] is Not Null
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

I have query using two linked tables. Table one has an agent code
(unique)
and the agents first name. Table two has the agent code (not unique)
and
a
freeform text field. I join the tables based on the agent code and want
to
search all records where the free form text field contains the agents
first
name. It wants to search on the field name rather than the contents of
the
field. The sql is:

SELECT MLSADMIN_PROP_1.MLS_ACCT, MLSADMIN_PROP_1.REMARKS,
MLSADMIN_MEMBERS.LA_FIRST_NAME
FROM MLSADMIN_MEMBERS INNER JOIN MLSADMIN_PROP_1 ON
MLSADMIN_MEMBERS.LA_LA_CODE = MLSADMIN_PROP_1.LA_CODE
WHERE (((MLSADMIN_PROP_1.REMARKS) Like "*[mlsadmin_members]![la_first
name]*"))
ORDER BY MLSADMIN_PROP_1.MLS_ACCT;

 

[Guest]

Whoops. Forgot to mention that these were linked tables into an Oracle
database. Once I got the concatenated text all upper case it worked like a
charm. Thanks both for the help.