Use "Not Like" on memo fields (Access 2K)

[Guest]

Can the "not like" operator be used to compare two fields that are memo type?
I ran a query to compare (using "not like") two fields of memo type. The
query returned fields that appeared to be identical. Can the "not like"
operator be used to compare memo type fields?

 

[Duane Hookom]

I might expect only the first 255 characters to be compared. I could be
wrong...

 

[Guest]

I might expect only the first 255 characters to be compared. I could be
wrong...

Duane Hookom,

Thanks for your help. I tried the "<>" operator and it worked. I guess the
"not like" operator does not work when one compare a memo type field to a
memo type fields. In fact the result implies that the fields are different
even when they are not. The "<>" operator on the other hand worked great.

Thanks for your help.

 

[Marshall Barton]

Can the "not like" operator be used to compare two fields that are memo type?
I ran a query to compare (using "not like") two fields of memo type. The
query returned fields that appeared to be identical. Can the "not like"
operator be used to compare memo type fields?

I just tested this on some fields where the only difference
started in character position 400 and it worked fine with
Like, Not Like and =

Note that using Like (or Not Like) without wildcards doesn't
make any sense, just use = or <>

OTOH, maybe your problem is that the field contains some
wildcard characters, in which case you are not really
comparing the two fields (a good reason why Like is
inappropriate unless you plan on the wildcards doing
something meaningful).

 

[Jamie Collins]

Note that using Like (or Not Like) without wildcards doesn't
make any sense

What about something like this:

iso_country_code LIKE '[A-Z][A-Z][A-Z]'

No wildcard characters and makes perfect sense.

Actually, avoiding wildcard character may be a good thing because they
are not interchangeable between 'ANSI Mode' and otherwise e.g. DAO = *
and ADO = %. The diligent profession will code for both e.g. this is
implementation-independent:

(data_col LIKE '*.*' OR data_col LIKE '%.%')
AND data_col <> '%.%'
AND data_col <> '*.*'

but than can be a pain when trying to do something succinct e.g. try
making this implementation-independent:

CompText NOT LIKE '%[!A-Z]%'.

Jamie.

--

 

[Marshall Barton]

I agree that wildcards vary from system to system and making
portable queries using Like is a real pain.

However, your example:
iso_country_code LIKE '[A-Z][A-Z][A-Z]'
is **all** wildcards as I'm sure you know if you think about
it for a second or two.

In prototype's question, one field might be "ABC" and
another could be "A[MDB]C" Clearly they are different
strings and will compare <>, BUT Like will report a match,
just as stated in the problem description.
--
Marsh
MVP [MS Access]

Note that using Like (or Not Like) without wildcards doesn't
make any sense

What about something like this:

iso_country_code LIKE '[A-Z][A-Z][A-Z]'

No wildcard characters and makes perfect sense.

Actually, avoiding wildcard character may be a good thing because they
are not interchangeable between 'ANSI Mode' and otherwise e.g. DAO = *
and ADO = %. The diligent profession will code for both e.g. this is
implementation-independent:

(data_col LIKE '*.*' OR data_col LIKE '%.%')
AND data_col <> '%.%'
AND data_col <> '*.*'

but than can be a pain when trying to do something succinct e.g. try
making this implementation-independent:

CompText NOT LIKE '%[!A-Z]%'.

 

[Jamie Collins]

However, your example:
iso_country_code LIKE '[A-Z][A-Z][A-Z]'
is **all** wildcards as I'm sure you know if you think about
it for a second or two.

I think I may have misaken your use of 'wildcards' for 'wildcard
characters'.

I'd suggest using 'wildcards' to mean 'pattern matching expressions'
invites confusion <g>.

I also though of another case where using LIKE without pattern matching
makes perfect sense:

LIKE '''' + <<scalar result which may be either a regular expression or
a literal string>> + ''''

but this may just be pedantry <g>.

Jamie.

--

 

[Marshall Barton]

However, your example:
iso_country_code LIKE '[A-Z][A-Z][A-Z]'
is **all** wildcards as I'm sure you know if you think about
it for a second or two.

I think I may have misaken your use of 'wildcards' for 'wildcard
characters'.

I'd suggest using 'wildcards' to mean 'pattern matching expressions'
invites confusion <g>.

I also though of another case where using LIKE without pattern matching
makes perfect sense:

LIKE '''' + <<scalar result which may be either a regular expression or
a literal string>> + ''''

but this may just be pedantry <g>.

Please, let's not go into regular expressions ;-)

But, as long as we're being pedantic ;-)
Access Help topic "Wildcard Characters" lists them as:

? or _ Any single character
* or % Zero or more characters
# Any single digit (0— 9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist

My point about using like without wildcards (above) is that
the right side operand must be verified as not containing
any wildcards, because Like will use them if they are there.

 

[Guest]

I just tested this on some fields where the only difference
started in character position 400 and it worked fine with
Like, Not Like and =

Note that using Like (or Not Like) without wildcards doesn't
make any sense, just use = or <>

OTOH, maybe your problem is that the field contains some
wildcard characters, in which case you are not really
comparing the two fields (a good reason why Like is
inappropriate unless you plan on the wildcards doing
something meaningful).

I greatly appreciate your help Marshall. I am a bit of a novice and thanks
to your help I have a better understanding of when to use “not like†and when
to use the trusted <> operator. In my case I was not using wildcards and just
wanted to compare two memo fields (i.e. [memoField1].[txt] not like
[memoField2].[txt]). I will now use "[memoField1].[txt] <>
[memoField2].[txt]" instead.

I would like to clarify one thing though. I performed the same test you did
but instead of having memo fields that were different I used memo fields that
were identical. Unfortunately in this case the “not like†operator returned a
false positive. The query indicated that the fields were not the same even
though they were. This is probably a moot point but I just wanted to clarify.

 

[Guest]

I just tested this on some fields where the only difference
started in character position 400 and it worked fine with
Like, Not Like and =

Note that using Like (or Not Like) without wildcards doesn't
make any sense, just use = or <>

OTOH, maybe your problem is that the field contains some
wildcard characters, in which case you are not really
comparing the two fields (a good reason why Like is
inappropriate unless you plan on the wildcards doing
something meaningful).

I greatly appreciate your help Marshall. I am a bit of a novice and thanks
to your help I have a better understanding of when to use “not like†and when
to use the trusted <> operator. In my case I was not using wildcards and just
wanted to compare two memo fields (i.e. [memoField1].[txt] not like
[memoField2].[txt]). I will now use "[memoField1].[txt] <>
[memoField2].[txt]" instead.

I would like to clarify one thing though. I performed the same test you did
but instead of having memo fields that were different I used memo fields that
were identical. Unfortunately in this case the “not like†operator returned a
false positive. The query indicated that the fields were not the same even
though they were. This is probably a moot point but I just wanted to clarify.

I would like to retract my last post. I now understand what Marshall was
saying about the right operand containing wildcards. If the right operand
contains a wildcard then they will be used. After further inspection the
right field I was comparing did indeed contain wildcards and this explains
the results. Sorry for any confusion and once again thanks for your help
Marshall.

 

[Marshall Barton]

prototype wrote:

Can the "not like" operator be used to compare two fields that are memo type?
I ran a query to compare (using "not like") two fields of memo type. The
query returned fields that appeared to be identical. Can the "not like"
operator be used to compare memo type fields?


I just tested this on some fields where the only difference
started in character position 400 and it worked fine with
Like, Not Like and =

Note that using Like (or Not Like) without wildcards doesn't
make any sense, just use = or <>

OTOH, maybe your problem is that the field contains some
wildcard characters, in which case you are not really
comparing the two fields (a good reason why Like is
inappropriate unless you plan on the wildcards doing
something meaningful).

I greatly appreciate your help Marshall. I am a bit of a novice and thanks
to your help I have a better understanding of when to use “not like” and when
to use the trusted <> operator. In my case I was not using wildcards and just
wanted to compare two memo fields (i.e. [memoField1].[txt] not like
[memoField2].[txt]). I will now use "[memoField1].[txt] <>
[memoField2].[txt]" instead.

I would like to clarify one thing though. I performed the same test you did
but instead of having memo fields that were different I used memo fields that
were identical. Unfortunately in this case the “not like” operator returned a
false positive. The query indicated that the fields were not the same even
though they were. This is probably a moot point but I just wanted to clarify.

I would like to retract my last post. I now understand what Marshall was
saying about the right operand containing wildcards. If the right operand
contains a wildcard then they will be used. After further inspection the
right field I was comparing did indeed contain wildcards and this explains
the results. Sorry for any confusion and once again thanks for your help
Marshall.

Don't apologize. This is a tricky subject and you're far
from the first person to be trapped by it. Think of it as a
well earned bit of new knowledge and be happy to chalk up
another "Learn something new every day" success ;-)

 

[Jamie Collins]

My point about using like without wildcards (above) is that
the right side operand must be verified as not containing
any wildcards, because Like will use them if they are there.

That is a good point. Here's a demo of how the row containing the
wildcard character '%' differs on the right side to that of the left
(if they were treated the same we'd get the same results as for the
equi-join):

Sub LikeIt()
Dim cat As Object
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"
With .ActiveConnection
..Execute _
"CREATE TABLE Test1 (" & _
" test_col NVARCHAR(3) NOT NULL);"
..Execute _
"INSERT INTO Test1 (test_col) VALUES ('ABC');"
..Execute _
"INSERT INTO Test1 (test_col) VALUES ('OPQ');"
..Execute _
"INSERT INTO Test1 (test_col) VALUES ('XYZ');"
..Execute _
"INSERT INTO Test1 (test_col) VALUES ('%');"
Dim rs As Object
Set rs = .Execute( _
" SELECT T1.test_col, T2.test_col " & _
" FROM Test1 AS T1 INNER JOIN Test1 AS T2" & _
" ON T1.test_col = T2.test_col " & _
" ORDER BY T2.test_col, T1.test_col; ")
MsgBox "EQUI-JOIN:" & vbCr & vbCr & rs.GetString
rs.Close
Set rs = .Execute( _
" SELECT T1.test_col, T2.test_col " & _
" FROM Test1 AS T1 INNER JOIN Test1 AS T2" & _
" ON T1.test_col LIKE T2.test_col " & _
" ORDER BY T2.test_col, T1.test_col; ")
MsgBox "LIKE JOIN:" & vbCr & vbCr & rs.GetString
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--