using wild card with criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I could not get wild card to work when specifying a criterion.
To illustrate,
I have Table1 with FiledName1 that has a value MAJOR EVENTS
I have Table2 with FieldName2 that has a value MAJOR EVENT

I apply the following criterion to the FiledName1 field from Table1:
Like [Table2]![FieldName2] & "*"

I expect ACCESS to find a match but it didn't.
Can anyone help me see why this doesn't work?

Regards,
David
 
You need to give us more info about the query that you tried. Post the SQL
statement of the query here so we can see what you're doing.
 
Hi Ken,

At the bottom is the SQL, only the table names are different, and this is an
update query and not a select query.

Table1 is zip1
Table 2 is Fltr2-SkipFirstName
When Zip1.OWNERFIRST is say: Williamson
and , [Fltr2-SkipFirstName]![FirstName] is say: William
I expect the query to identify the above as a match (and carry out the
query update).
But the query did not identify this as a match, and no update occurs.

Here is the SQL:
UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName] ON zip1.OWNERFIRST =
[Fltr2-SkipFirstName].FirstName SET zip1.Mark1stName = "SkipThis1stName"
WHERE (((zip1.OWNERFIRST) Like "*" & [Fltr2-SkipFirstName]![FirstName] &
"*"));

David


Ken Snell said:
You need to give us more info about the query that you tried. Post the SQL
statement of the query here so we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>

David said:
Hi,
I could not get wild card to work when specifying a criterion.
To illustrate,
I have Table1 with FiledName1 that has a value MAJOR EVENTS
I have Table2 with FieldName2 that has a value MAJOR EVENT

I apply the following criterion to the FiledName1 field from Table1:
Like [Table2]![FieldName2] & "*"

I expect ACCESS to find a match but it didn't.
Can anyone help me see why this doesn't work?

Regards,
David
Click to expand...
Click to expand...
 
Pardon me for jumping in.

Your JOIN clause is eliminating the matches before you ever get to the where clause.

You can either use a non-equi join or drop the join altogether. In either
case, you might end up with a non-updateable query.

UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName]
ON zip1.OWNERFIRST LIKE [Fltr2-SkipFirstName].FirstName & "*"
SET zip1.Mark1stName = "SkipThis1stName"

Try this on a COPY of your data to see if it does what you want.
Hi Ken,

At the bottom is the SQL, only the table names are different, and this is an
update query and not a select query.

Table1 is zip1
Table 2 is Fltr2-SkipFirstName
When Zip1.OWNERFIRST is say: Williamson
and , [Fltr2-SkipFirstName]![FirstName] is say: William
I expect the query to identify the above as a match (and carry out the
query update).
But the query did not identify this as a match, and no update occurs.

Here is the SQL:
UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName] ON zip1.OWNERFIRST =
[Fltr2-SkipFirstName].FirstName SET zip1.Mark1stName = "SkipThis1stName"
WHERE (((zip1.OWNERFIRST) Like "*" & [Fltr2-SkipFirstName]![FirstName] &
"*"));

David

Ken Snell said:
You need to give us more info about the query that you tried. Post the SQL
statement of the query here so we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>

David said:
Hi,
I could not get wild card to work when specifying a criterion.
To illustrate,
I have Table1 with FiledName1 that has a value MAJOR EVENTS
I have Table2 with FieldName2 that has a value MAJOR EVENT

I apply the following criterion to the FiledName1 field from Table1:
Like [Table2]![FieldName2] & "*"

I expect ACCESS to find a match but it didn't.
Can anyone help me see why this doesn't work?

Regards,
David
Click to expand...
Click to expand...
Click to expand...
 
The SQL I had it originally is actually able to pick up "exact" matches.
Meaning when the first table has 100 rows with "William" as the FirstName,
and if I include "William" as one of the entry in the second table's
FirtName column as well,
my original SQL will pick up all the 100 rows correctly.
However, the "Williamson" in the First table does not get pick up.

John: I have tried your suggestion on a modified querry. The modified query
picks up nothing. Even when there is an exact match.

What seems to be the problem?

David



John Spencer (MVP) said:
Pardon me for jumping in.

Your JOIN clause is eliminating the matches before you ever get to the where clause.

You can either use a non-equi join or drop the join altogether. In either
case, you might end up with a non-updateable query.

UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName]
ON zip1.OWNERFIRST LIKE [Fltr2-SkipFirstName].FirstName & "*"
SET zip1.Mark1stName = "SkipThis1stName"

Try this on a COPY of your data to see if it does what you want.
Hi Ken,

At the bottom is the SQL, only the table names are different, and this is an
update query and not a select query.

Table1 is zip1
Table 2 is Fltr2-SkipFirstName
When Zip1.OWNERFIRST is say: Williamson
and , [Fltr2-SkipFirstName]![FirstName] is say: William
I expect the query to identify the above as a match (and carry out the
query update).
But the query did not identify this as a match, and no update occurs.

Here is the SQL:
UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName] ON zip1.OWNERFIRST =
[Fltr2-SkipFirstName].FirstName SET zip1.Mark1stName = "SkipThis1stName"
WHERE (((zip1.OWNERFIRST) Like "*" & [Fltr2-SkipFirstName]![FirstName] &
"*"));

David

Ken Snell said:
You need to give us more info about the query that you tried. Post the SQL
statement of the query here so we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I could not get wild card to work when specifying a criterion.
To illustrate,
I have Table1 with FiledName1 that has a value MAJOR EVENTS
I have Table2 with FieldName2 that has a value MAJOR EVENT

I apply the following criterion to the FiledName1 field from Table1:
Like [Table2]![FieldName2] & "*"

I expect ACCESS to find a match but it didn't.
Can anyone help me see why this doesn't work?

Regards,
David
Click to expand...
Click to expand...
Click to expand...
 
John inadvertently omitted one of the wild cards when he rewrote your query.
Try this:

UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName]
ON zip1.OWNERFIRST LIKE "*"" & [Fltr2-SkipFirstName].FirstName & "*"
SET zip1.Mark1stName = "SkipThis1stName"


--

Ken Snell
<MS ACCESS MVP>

David said:
The SQL I had it originally is actually able to pick up "exact" matches.
Meaning when the first table has 100 rows with "William" as the FirstName,
and if I include "William" as one of the entry in the second table's
FirtName column as well,
my original SQL will pick up all the 100 rows correctly.
However, the "Williamson" in the First table does not get pick up.

John: I have tried your suggestion on a modified querry. The modified query
picks up nothing. Even when there is an exact match.

What seems to be the problem?

David



John Spencer (MVP) said:
Pardon me for jumping in.

Your JOIN clause is eliminating the matches before you ever get to the where clause.

You can either use a non-equi join or drop the join altogether. In either
case, you might end up with a non-updateable query.

UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName]
ON zip1.OWNERFIRST LIKE [Fltr2-SkipFirstName].FirstName & "*"
SET zip1.Mark1stName = "SkipThis1stName"

Try this on a COPY of your data to see if it does what you want.
Hi Ken,

At the bottom is the SQL, only the table names are different, and this is an
update query and not a select query.

Table1 is zip1
Table 2 is Fltr2-SkipFirstName
When Zip1.OWNERFIRST is say: Williamson
and , [Fltr2-SkipFirstName]![FirstName] is say: William
I expect the query to identify the above as a match (and carry out the
query update).
But the query did not identify this as a match, and no update occurs.

Here is the SQL:
UPDATE zip1 INNER JOIN [Fltr2-SkipFirstName] ON zip1.OWNERFIRST =
[Fltr2-SkipFirstName].FirstName SET zip1.Mark1stName = "SkipThis1stName"
WHERE (((zip1.OWNERFIRST) Like "*" & [Fltr2-SkipFirstName]![FirstName] &
"*"));

David

:

You need to give us more info about the query that you tried. Post the SQL
statement of the query here so we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>

Hi,
I could not get wild card to work when specifying a criterion.
To illustrate,
I have Table1 with FiledName1 that has a value MAJOR EVENTS
I have Table2 with FieldName2 that has a value MAJOR EVENT

I apply the following criterion to the FiledName1 field from Table1:
Like [Table2]![FieldName2] & "*"

I expect ACCESS to find a match but it didn't.
Can anyone help me see why this doesn't work?

Regards,
David
Click to expand...
Click to expand...
Click to expand...
 
Back
Top