Correct Syntax For "Not Like"

[bidbud68]

Hi I would like to take the SQL from my Access Query as follows however the syntax is not correct for the Not Like conditions, is there any other format I can use ?

SELECT DISTINCT Accounts.ReservationID, Reservations.ReservationID, Reservations.[SalesPerson Email], Customers.EmailAddress, Accounts.Date, Accounts.Item
FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer
WHERE (((Accounts.Item) Not Like "*Payment*" And (Accounts.Item) Not Like "*Trf*" And (Accounts.Item) Not Like "*Deposit Received*" And (Accounts.Item) Not Like "*Deposit Reversed*"));

[alansidman]

Try changing your Where condition to Or instead of And. Your statement now says that all the conditions need to be met to get a result. Is this what you meant or did you mean that any one of the conditions need to be met? If the latter, then change the ands to ors.

[Bob Fitz]

Have you considered using <>

[John_G]

Hi -

I don't see anything wrong with that syntax. Where is it that it does not work - in Access or somewhere else?

John

[bidbud68]

It works in an Access Query but when I copy the SQL to VBA it doesnt work

[John_G]

It will work fine in VBA, but you have to use it a bit differently. Since it is a select query, about all you can do with it is create a recordset that you will manipulate further. The VBA would look something like this:

Dim db as Database, rst as RecordSet, SQL as string
set db=currentdb


SQL = "SELECT DISTINCT Accounts.ReservationID, Reservations.ReservationID, Reservations.[SalesPerson Email], Customers.EmailAddress, Accounts.Date, Accounts.Item FROM Customers INNER JOIN (Accounts INNER JOIN Reservations ON Accounts.ReservationID = Reservations.ReservationID) ON Customers.CompanyName = Reservations.Customer WHERE (((Accounts.Item) Not Like '*Payment*' And (Accounts.Item) Not Like '*Trf*' And (Accounts.Item) Not Like '*Deposit Received*' And (Accounts.Item) Not Like '*Deposit Reversed*'))
Set rst = db.OpenRecordSet(SQL)
While not rst.EOF
.
. Code to manipulate the data
'
rst.MoveNext
Wend

Note that I changed the double-quotes within the SQL to single quotes, to make it easier to read; SQL allows that.

HTH

John

[bidbud68]

Thank you I tried copying it again from the access query SQL and it works.

[RonL]

It will work fine in VBA, but you have to use it a bit differently. Since it is a select query, about all you can do with it is create a recordset that you will manipulate further. ..............

I have an ancillary (and basic) question, since I'm about to deal with something similar. It relates to my lack of fundamental understanding of the object model, in particular, recordsets.

When you say "...create a recordset..." is "create" - ie. instantiation - meant literally? Or is it really just an *assignment* of a variable to the query's result?

The reason I'm confused is, from my reading, one must adhere to a similar syntax even when the query has already been defined and is stored in the database - eg. can be seen in the project navigator. So in the VBA statements, why are we "creating" the recordset when it already exists??

Is not the "set rst..." statement simply an assignment of an existing object to an object variable,and not an instantiation? If it is an instantiation, then how does the manipulation of the new object get reflected back to the original object, eg. the recordset we see on the form whose control source is the query, for example?

Would appreciate any appropriate link to documentation in this area.

Thanks, -Ron