Comparing Fields using Wildcards

[Guest]

I am trying to associate the part no. of products in one table to their pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

[Guest]

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I am trying to associate the part no. of products in one table to their pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

[Guest]

Thanks for the quick reply...

Both tblImages.Img & tblDS.Part No are text strings

Access 2003 is the only DB in use here.

The SQL commands included in this message was generated by Access from the
query I made.

Thanks- I hope this makes things clearer.

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I am trying to associate the part no. of products in one table to their pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

['69 Camaro]

Hi.

Things to try:

1.) Change the "*" wildcard to "%"

If that doesn't show any records when you run the query, then:

2.) Create a new query and paste this into the SQL pane:

SELECT Img_ID, ImgID
FROM tblDS INNER JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID;

Does that show any records when you run the query?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Thanks for the quick reply...

Both tblImages.Img & tblDS.Part No are text strings

Access 2003 is the only DB in use here.

The SQL commands included in this message was generated by Access from the
query I made.

Thanks- I hope this makes things clearer.

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

I am trying to associate the part no. of products in one table to their
pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

[David F Cox]

Try ((TRIM((tblImages.Img)) Like TRIM([tblDS].[Part No]) & "*"));

 

[Guest]

Well, there isn't any records showing there, because no Img_ID fields have
been inserted, yet.

What I have is a table of parts, and a text list of pics that go with those
parts, and I need to associate them for a store front on a website. The
names of the pics have the complete part number at the begining of their text
string (with an added alpha-numeric character or two at the end every so
often) as well as the .jpg extension.

Here's the problem: The part numbers vary in length, and the pic names
often have the added characters mentioned above.

So..can I use a query to compare the text strings to associate the part
numbers to the pic names?

Hi.

Things to try:

1.) Change the "*" wildcard to "%"

If that doesn't show any records when you run the query, then:

2.) Create a new query and paste this into the SQL pane:

SELECT Img_ID, ImgID
FROM tblDS INNER JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID;

Does that show any records when you run the query?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Thanks for the quick reply...

Both tblImages.Img & tblDS.Part No are text strings

Access 2003 is the only DB in use here.

The SQL commands included in this message was generated by Access from the
query I made.

Thanks- I hope this makes things clearer.

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


:

I am trying to associate the part no. of products in one table to their
pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

[Guest]

Hi.

Well, there isn't any records showing there, because no Img_ID fields have
been inserted, yet.

Okay. That explains why you don't see any records in your query. The query
is listing all the records in the Img_ID column and any matching records in
the ImgID column. If there aren't any values in the Img_ID column, no
records will show, because no records meet the join criteria.

Here's the problem: The part numbers vary in length, and the pic names
often have the added characters mentioned above.

So..can I use a query to compare the text strings to associate the part
numbers to the pic names?

Yes. Add some test data and you'll see that the criteria in your WHERE
clause brings up the records you think it will when the criteria in the JOIN
clause is met. You had the correct query. You just didn't have the data in
the tables to show you that you were right.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Well, there isn't any records showing there, because no Img_ID fields have
been inserted, yet.

What I have is a table of parts, and a text list of pics that go with those
parts, and I need to associate them for a store front on a website. The
names of the pics have the complete part number at the begining of their text
string (with an added alpha-numeric character or two at the end every so
often) as well as the .jpg extension.

Here's the problem: The part numbers vary in length, and the pic names
often have the added characters mentioned above.

So..can I use a query to compare the text strings to associate the part
numbers to the pic names?

Hi.

Things to try:

1.) Change the "*" wildcard to "%"

If that doesn't show any records when you run the query, then:

2.) Create a new query and paste this into the SQL pane:

SELECT Img_ID, ImgID
FROM tblDS INNER JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID;

Does that show any records when you run the query?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Thanks for the quick reply...

Both tblImages.Img & tblDS.Part No are text strings

Access 2003 is the only DB in use here.

The SQL commands included in this message was generated by Access from the
query I made.

Thanks- I hope this makes things clearer.



:

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


:

I am trying to associate the part no. of products in one table to their
pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.

 

[Guest]

You're absolutely right!

Now I realize that I've really been asking the wrong question. I have these
two long lists that need to be joined, but I have to give them a frame of
reference in order to do that. The query works fine as long as the records
are joined, but how do I use this query on a long list that is not yet in the
data base or associated with a certain part except in two separate tables?

Hi.

Well, there isn't any records showing there, because no Img_ID fields have
been inserted, yet.

Okay. That explains why you don't see any records in your query. The query
is listing all the records in the Img_ID column and any matching records in
the ImgID column. If there aren't any values in the Img_ID column, no
records will show, because no records meet the join criteria.

Here's the problem: The part numbers vary in length, and the pic names
often have the added characters mentioned above.

So..can I use a query to compare the text strings to associate the part
numbers to the pic names?

Yes. Add some test data and you'll see that the criteria in your WHERE
clause brings up the records you think it will when the criteria in the JOIN
clause is met. You had the correct query. You just didn't have the data in
the tables to show you that you were right.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Well, there isn't any records showing there, because no Img_ID fields have
been inserted, yet.

What I have is a table of parts, and a text list of pics that go with those
parts, and I need to associate them for a store front on a website. The
names of the pics have the complete part number at the begining of their text
string (with an added alpha-numeric character or two at the end every so
often) as well as the .jpg extension.

Here's the problem: The part numbers vary in length, and the pic names
often have the added characters mentioned above.

So..can I use a query to compare the text strings to associate the part
numbers to the pic names?

Hi.

Things to try:

1.) Change the "*" wildcard to "%"

If that doesn't show any records when you run the query, then:

2.) Create a new query and paste this into the SQL pane:

SELECT Img_ID, ImgID
FROM tblDS INNER JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID;

Does that show any records when you run the query?

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


Thanks for the quick reply...

Both tblImages.Img & tblDS.Part No are text strings

Access 2003 is the only DB in use here.

The SQL commands included in this message was generated by Access from the
query I made.

Thanks- I hope this makes things clearer.



:

Hi.

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

What data type is tblImages.Img? OLE object or text string? The query
needs to compare string values in order to use the LIKE operator.

If it's a text string, then are you using SQL Server compatible syntax? Jet
uses * for the wildcard, but SQL Server uses % to match a string of zero or
more characters.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


:

I am trying to associate the part no. of products in one table to their
pics
in another.
example:

PN PIC

ds11300 ds11300a.jpg
ds1127665 ds1127665.jpg
ds112983 ds112983as.jpg

The relationship between the tables is an auto number in the pic table
linked to a column in the Parts' table called Img_ID. I'm trying to run a
query which translates into the following SQL:

SELECT tblDS.[Part No], tblImages.Img
FROM tblDS LEFT JOIN tblImages ON tblDS.Img_ID = tblImages.ImgID
WHERE (((tblImages.Img) Like [tblDS].[Part No] & "*"));

I'm not getting any errors, but no records are returned, either.

I'm trying to use the part number and a wildcard with the like operator in
the criteria so I can update the Img_ID s for my parts and move on to the
next obstacle. Any help would be greatly appreciated.