CODEWITHSUNDEEP
vbams-access
didacticone
didacticone

Reputation: 3

VBA operator Not Like

I am using a filter on a form currently:

Me.Filter = "[duedate] = '" & Me.duedatesearch & "' And [employeename] Is Null"
Me.FilterOn = True

This works fine. What I am struggling with is trying to exclude a result from a different field called "workingfor". For example if the above criteria is met AND workingfor is not like "Homer". Not like doesn't seem to be a valid VBA operator. Any ideas?

Thank you.

I have tried the following:

Me.Filter = "[duedate] = '" & Me.duedatesearch & "' And [employeename] Is Null" And Not [workingfor] Like "Homer"
Me.FilterOn = True

Which returns Run-time error '13': Type mismatch

Upvotes: 0

Views: 2934

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

A couple of issues with your current code:

  • As you'll observe from the syntax highlighting when posting your question, the last section of your filter is not part of the initial string, as you have a double-quote after Null.

  • Unless you were to enclose the Like expression within parentheses, the appropriate operator is Not Like.

For example:

Me.Filter = "[duedate] = '" & Me.duedatesearch & "' And [employeename] Is Null And [workingfor] Not Like 'Homer'"
Me.FilterOn = True

However, since you are not using any wildcard operators within the Like pattern, this could alternatively be written using the not equal operator (<>):

Me.Filter = "[duedate] = '" & Me.duedatesearch & "' And [employeename] Is Null And [workingfor] <> 'Homer'"
Me.FilterOn = True

Aside, I find it odd that your field [duedate] appears to be a string-valued field, rather than a Date field.

Upvotes: 2

Related Questions