Comparing using "like"

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have one table with a list of standard suffixes.
I have a second table with a few hundred items in it, and
I want to ensure each of those items ends in a suffix. I
can hard code the suffixes into a query using the "like"
function, but I need to use a table as the list of
suffixes changes.

Thanks
 
You might use a query (say, named "Query1") whose SQL looks something like
this to find records in your table where your field does end in a standard
suffix:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
EXISTS
(SELECT
Null
FROM
[Suffixes]
WHERE
[Your Table].[Your Field] Like "*" & [Suffixes].[Suffix])

To find the records in your table where your field does not end in a
standard suffix, you might use a query based Query1 and whose SQL looks
something like this:

SELECT
[Your Table].*
FROM
[Your Table]
LEFT JOIN
[Query1]
ON
[Your Table].[Your Primary Key Field] = [Query1].[Your Primary Key Field]
WHERE
[Query1].[Your Primary Key Field] IS NULL

If you want to enforce the constraint that your field in your table always
ends in a standard suffix, your best bet may be to move the suffix in your
field into a separate field, and then create a relationship with enforced
referential integrity on that field to the suffixes table. You can always
put the fields back together for display purposes using a calculated field
in a query, form, report, etc.
 
Brian,

Thanks... I entered your SQL and looked at the QBE screen;
where I saw "False". I changed it to "true" and the query
works fine... I didn't need the second query at all.

Scott
-----Original Message-----
You might use a query (say, named "Query1") whose SQL looks something like
this to find records in your table where your field does end in a standard
suffix:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
EXISTS
(SELECT
Null
FROM
[Suffixes]
WHERE
[Your Table].[Your Field] Like "*" & [Suffixes].[Suffix])

To find the records in your table where your field does not end in a
standard suffix, you might use a query based Query1 and whose SQL looks
something like this:

SELECT
[Your Table].*
FROM
[Your Table]
LEFT JOIN
[Query1]
ON
[Your Table].[Your Primary Key Field] = [Query1].[Your Primary Key Field]
WHERE
[Query1].[Your Primary Key Field] IS NULL

If you want to enforce the constraint that your field in your table always
ends in a standard suffix, your best bet may be to move the suffix in your
field into a separate field, and then create a relationship with enforced
referential integrity on that field to the suffixes table. You can always
put the fields back together for display purposes using a calculated field
in a query, form, report, etc.

I have one table with a list of standard suffixes.
I have a second table with a few hundred items in it, and
I want to ensure each of those items ends in a suffix. I
can hard code the suffixes into a query using the "like"
function, but I need to use a table as the list of
suffixes changes.

Thanks
Click to expand...


.
Click to expand...
 
Back
Top