Not Like

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

This isn't returning a value. I think its the combination of two
different Not In and Not Like.
Any help appreciated.
Thanks
DS

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE (((tblTabTypes.TabTypeNumber) Not In (6,9,10,11)
And (tblTabTypes.TabTypeNumber) Not Like
[Forms]![frmFXCheckType]![TxtTypeID]))
ORDER BY tblTabTypes.TabTypeName;
 
Is TabTypeNumber a text field or is it a number field? You are treating it
as both in your code.

Access will attempt to convert a Number to a string if you use the Like
operator. What is the Value of txtTypeID? If it is a wild card character
then you are excluding all records.

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE tblTabTypes.TabTypeNumber Not In (6,9,10,11)
And tblTabTypes.TabTypeNumber Not Like
[Forms]![frmFXCheckType]![TxtTypeID]
ORDER BY tblTabTypes.TabTypeName;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
This isn't returning a value. I think its the combination of two
different Not In and Not Like.
Any help appreciated.
Thanks
DS

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE (((tblTabTypes.TabTypeNumber) Not In (6,9,10,11)
And (tblTabTypes.TabTypeNumber) Not Like
[Forms]![frmFXCheckType]![TxtTypeID]))
ORDER BY tblTabTypes.TabTypeName;
Click to expand...

The LIKE operator uses wildcards to search substrings. You don't obviously
have any wildcards here - what are the contents of the textbox txttTypeID? Are
you trying to *exclude* typenumber values 6, 9, 10, 11 and a single
user-entered value, or what?

John W. Vinson [MVP]
 
John said:
Is TabTypeNumber a text field or is it a number field? You are treating it
as both in your code.

Access will attempt to convert a Number to a string if you use the Like
operator. What is the Value of txtTypeID? If it is a wild card character
then you are excluding all records.

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE tblTabTypes.TabTypeNumber Not In (6,9,10,11)
And tblTabTypes.TabTypeNumber Not Like
[Forms]![frmFXCheckType]![TxtTypeID]
ORDER BY tblTabTypes.TabTypeName;
Click to expand...
These are all Number Fields
DS
 
John said:
This isn't returning a value. I think its the combination of two
different Not In and Not Like.
Any help appreciated.
Thanks
DS

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
Click to expand...
FROM tblTabTypes
Click to expand...
WHERE (((tblTabTypes.TabTypeNumber) Not In (6,9,10,11)
And (tblTabTypes.TabTypeNumber) Not Like
[Forms]![frmFXCheckType]![TxtTypeID]))
ORDER BY tblTabTypes.TabTypeName;
Click to expand...


The LIKE operator uses wildcards to search substrings. You don't obviously
have any wildcards here - what are the contents of the textbox txttTypeID? Are
you trying to *exclude* typenumber values 6, 9, 10, 11 and a single
user-entered value, or what?

John W. Vinson [MVP]
Click to expand...
I'm trying to exclude the numbers 6,9,10,11 And a Number that will be in
the TxtTypeID field.
Thanks
DS
 
I'm trying to exclude the numbers 6,9,10,11 And a Number that will be in
the TxtTypeID field.
Click to expand...

Well, you're making it much harder than it needs to be.

Don't use the LIKE operator *at all* unless you are using wildcards to search
a portion of a Text string.

In your case, all you need is

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE tblTabTypes.TabTypeNumber
Not In (6,9,10,11, [Forms]![frmFXCheckType]![TxtTypeID])
ORDER BY tblTabTypes.TabTypeName;

John W. Vinson [MVP]
 
John said:
I'm trying to exclude the numbers 6,9,10,11 And a Number that will be in
the TxtTypeID field.
Click to expand...


Well, you're making it much harder than it needs to be.

Don't use the LIKE operator *at all* unless you are using wildcards to search
a portion of a Text string.

In your case, all you need is

SELECT tblTabTypes.TabTypeNumber, tblTabTypes.TabTypeName
FROM tblTabTypes
WHERE tblTabTypes.TabTypeNumber
Not In (6,9,10,11, [Forms]![frmFXCheckType]![TxtTypeID])
ORDER BY tblTabTypes.TabTypeName;

John W. Vinson [MVP]
Click to expand...
Thanks John,
I appreciate it.
DS
 
Back
Top