Not Like

D

DS

This statement works if I only use one Not Like, the minute I add a
second or more Not Likes it stops filtering and gives me every record!
I tried this in a Query also. One Not Like works but add another and it
gives me every record.
Any help appreciated.
Thanks
DS

With Me.CmbDepositType
..RowSource = "SELECT PayName.PaymentNameID, PayName.PaymentName, " & _
"PayName.PaymentType " & _
"FROM PayName " & _
"WHERE (((PayName.PaymentType) Not Like ""3"")) " & _
"OR (((PayName.PaymentType) Not Like ""7"")) " & _
"OR (((PayName.PaymentType) Not Like ""9"")) " & _
"OR (((PayName.PaymentType) Not Like ""10"")) " & _
"OR (((PayName.PaymentType) Not Like ""11"")) " & _
"ORDER BY PayName.PaymentName;"
..Requery
End With
 
D

Douglas J. Steele

You need to AND them together, not OR them.

The first one is going to stop you from getting any where PaymentType is 3,
but since 3 isn't equal to 7, the second one will let it through.

BTW, since you're not using wildcards, there's no reason to be using Like: a
simple = would suffice.

.RowSource = "SELECT PayName.PaymentNameID, PayName.PaymentName, " & _
"PayName.PaymentType " & _
"FROM PayName " & _
"WHERE (((PayName.PaymentType) <> ""3"")) " & _
"AND (((PayName.PaymentType) <> ""7"")) " & _
"AND (((PayName.PaymentType) <> ""9"")) " & _
"AND (((PayName.PaymentType) <> ""10"")) " & _
"AND (((PayName.PaymentType) <> ""11"")) " & _
"ORDER BY PayName.PaymentName;"

In fact, you could use NOT IN:

.RowSource = "SELECT PayName.PaymentNameID, PayName.PaymentName, " & _
"PayName.PaymentType " & _
"FROM PayName " & _
"WHERE PayName.PaymentType Not In " & _
"(""3"", ""7"", ""9"", ""10"", ""11"") " & _
"ORDER BY PayName.PaymentName;"
 
D

DS

Douglas said:
You need to AND them together, not OR them.

The first one is going to stop you from getting any where PaymentType is 3,
but since 3 isn't equal to 7, the second one will let it through.

BTW, since you're not using wildcards, there's no reason to be using Like: a
simple = would suffice.

.RowSource = "SELECT PayName.PaymentNameID, PayName.PaymentName, " & _
"PayName.PaymentType " & _
"FROM PayName " & _
"WHERE (((PayName.PaymentType) <> ""3"")) " & _
"AND (((PayName.PaymentType) <> ""7"")) " & _
"AND (((PayName.PaymentType) <> ""9"")) " & _
"AND (((PayName.PaymentType) <> ""10"")) " & _
"AND (((PayName.PaymentType) <> ""11"")) " & _
"ORDER BY PayName.PaymentName;"

In fact, you could use NOT IN:

.RowSource = "SELECT PayName.PaymentNameID, PayName.PaymentName, " & _
"PayName.PaymentType " & _
"FROM PayName " & _
"WHERE PayName.PaymentType Not In " & _
"(""3"", ""7"", ""9"", ""10"", ""11"") " & _
"ORDER BY PayName.PaymentName;"
Click to expand...
Sounds Good! I just tried it and I'm getting a Data Type error. The
PaymentType field is a number field. What could be causing this?
Thnaks
DS
 
D

DS

DS said:
Sounds Good! I just tried it and I'm getting a Data Type error. The
PaymentType field is a number field. What could be causing this?
Thnaks
DS
Click to expand...
NeverMind. I removed the quotes and all is well in Data land.
Douglas thank you for your help! Much appreciated.
Thnaks
DS
 
D

Douglas J. Steele

DS said:
Sounds Good! I just tried it and I'm getting a Data Type error. The
PaymentType field is a number field. What could be causing this?
Thnaks
DS
Click to expand...

As you've already found, since PaymentType is numeric, you don't need
quotes. I'm surprised Access didn't complain when you were using the Like on
the numeric fields, but I guess it was coercing the data types under the
cover.
 
Top