Criteria Query: Not like

You can use NOT LIKE in a criteria....
Where are you running this SQL from?
Is it an object, or are you running from a form?

If it's from a form, you can use CASE to build your statement, based on the user selecting "Like" or "NOT LIKE" from a combo box or option list.

Idea?

If you try this ffrom a SQL point of view, I Think this should work:

SELECT * FROM Table WHERE FieldName NOT LIKE 'New Deals%' AND FieldName <> "Funded" AND FieldName <> "withdrawn"

NOT is a rather tricky way to get the information.
In general I specify a "positive selection" and after surrounding it by ( and ) then put NOT infront of it.
So try:
WHERE NOT ([Deals] like 'New deals*')

Nic;o)

why not try a subquery?

not sure if that what Nico is talking about, something like this

select *
from table
where myfield <> "withdrawn"
and myfield <> "funded"
and mytable_id not in (select mytable_id where myfield like 'New Deals*')



alterntive apporach might be the IN command, not sure if you can use NOT with it
e.g.
select * from mytable where myfield IN ('funded','withdrawn','new deals','new deals on hold')
so with not try this

select * from mytable where myfield not IN ('funded','withdrawn','new deals','new deals on hold')

Nico,

I like your answer but I cant seem to get it to work.  I am a novice at Access.  I am not a programmer either.  Is "Deals" the name of the fieldname?  I put your statement in the criteria of the query on my form but gives me no hits.  

thank you

Yes, [Deals] is what I interpreted as the name holding the values "New Deals", "New Deals on Hold", etc.
But I don't know the fieldname you use as it's not in your "WHERE" statement.

So lets assume it's [Status]. A "valid" WHERE would look then like:
WHERE [Status] <>"withdrawn" And [Status] <>"Funded" And [Status] <>"New Deals" And [Status] <>"New Deals on Hold"
or better:
WHERE NOT ([Status] IN ("withdrawn", "Funded", "New Deals", "New Deals on Hold") )

Having multiple different values for one field tested is best done using the "IN" and between the ( and ) a comma separated list of the values.

Can you give a list of all values you need to exclude and the fieldname from the table that hold these ?

Nic;o)

Nico,

The way I have it written now after your last email is
WHERE NOT ([Status] IN ("Claim", "Denied", "Withdrawn", "Funded", "New Deals", "New Deals on Hold") )

but when I click out of the box it gives me an error "You may have entered an operator, such as the + operator, in an exporession without an operand.  

thank you

Can you post the complete query SQL text ?

Nic;o)

Nico,

it is big...

SELECT tblDeals.*, tblExporters.Name, tblLetterOfCredit.*, tblBrokers.Name, tblBorrower.Name, tblBorrower.APONumber FROM ((tblBorrower RIGHT JOIN (tblBrokers RIGHT JOIN tblDeals ON tblBrokers.BrokerID=tblDeals.BrokerID) ON tblBorrower.DealID=tblDeals.DealID) LEFT JOIN tblLetterOfCredit ON tblDeals.DealID=tblLetterOfCredit.DealID) LEFT JOIN tblExporters ON tblDeals.DealID=tblExporters.DealID WHERE (((tblExporters.Name) Not Like Forms!frmReportsMain!NotLike) And ((tblDeals.Status)<>"Denied" And (tblDeals.Status)<>"Claim" And (tblDeals.Status)<>"withdrawn" And (tblDeals.Status)<>"Funded" And (tblDeals.Status)<>"New Deals" And (tblDeals.Status)<>"New Deals - on Hold") And ((tblLetterOfCredit.LCFeesPaid)=Forms!frmReportsMain!LCFeesPaid) And ((tblLetterOfCredit.[PolicyRec'd])=Forms!frmReportsMain!PolicyReceived) And ((tblLetterOfCredit.ArrangeFeePaid)=Forms!frmReportsMain!ArrangeFeePaid) And ((tblLetterOfCredit.InvoicedforLegalPaid)=Forms!frmReportsMain!InvoicedforLegalPaid) And ((tblLetterOfCredit.InvoicedGoodFaithDepositPaid)=Forms!frmReportsMain!InvoicedGoodFaithDepositPaid)) ORDER BY tblBorrower.Name, tblDeals.Status DESC;


thank you

Well, I thank you for your help.  I had problems with that too.  I will continue with my old way.  

thank you