Filtering a form with Is Not Like

[ECEK]

Could just do with the VBA form filtering a form (on load) that excludes one item in a field.

DoCmd.OpenForm "MyForm", acFormDS, "", , , acNormal

I want to filter WHERE [Field1] Is Not Like "Completed"

Therefore:

DoCmd.OpenForm "MyForm", acFormDS, "",WHERE [Field1] Is Not Like "Completed" , , acNormal

Could you point me in the right direction please

I will also need to have a button on the form that reverses this i.e. it shows all the completed (Im guessing that that would be to just remove the filter)

Thanks for your help

 

[Minty]

You need to escape the text for the criteria so something like;

DoCmd.OpenForm "YourForm", acNormal, , "[Field1] <> 'Completed'"

Be careful if you have null values - this won't pick them up.

 

[isladogs]

And for the opposite button, replace <> with =
Removing the filter as you suggested will give you all the records.

 

[ECEK]

Thanks guys this is great. Due to the filtering that I need when Im on my form, I need to find a Me.Filter solution.

I need to create a multiple Me.Filter but Im struggling.

Me.Filter = "Field1 <>'Completed'" And "Field2 = """ & [Forms]![frmSettings]![User] & """"
Me.FilterOn = True

The second filter from a hidden form and works when in isolation.

 

[MajP]

I always do this so I can error check

dim strWhere as string
strWhere = = "Field1 <>'Completed'" And "Field2 = """ & [Forms]![frmSettings]![User] & """"
debug.print strWhere
'check your string is it returning what you want
me.Filter = strwhere

I would do this "'" instead of """

"Field1 <>'Completed' And Field2 = '" & [Forms]![frmSettings]![User] & "'"

 

[ECEK]

This is the problem

"Field1 <>'Completed'" And "Field2 = """ & [Forms]![frmSettings]![User] & """"

Is everybody sure that this is the correct way to structure multiple filters?

 

[MajP]

Did you read my post? Yes that is not correct.

 

[The_Doc_Man]

On a different tangent, just for clarification purposes:

There is no difference in effect between the next two statements (other that the first one is wrong syntax).

WHERE [Field1] Is Not Like "Completed"
WHERE [Field1] <> "Completed"

Since there are no wild card characters in your comparison string, the NOT LIKE relational operator is just a slower way to write the <> relational operator. Not to mention that "IS" does not belong in that part of a where clause. You would not write this as "field IS = 'constant' " or "field IS NOT = 'constant' ". You just use = or <> and let it go at that. There IS no shortcut symbolic operator for LIKE, so you would use "Field Not Like 'constant'" and let it run.

The advice you are getting on the other matters is fine so I'll back away to keep from stirring up the pot too much.

 

[Minty]

MajP advice is good - create the string then look at it. The AND needs to be part of the filter string.

strWhere = "Field1 <>'Completed' And Field2 = '" & [Forms]![frmSettings]![User] & "' "

I prefer the single quotes unless you have a user ID called O'Smith in which case you where on the right track with the triple quotes, just much harder to debug.

 

[MajP]

I alwasy debug, because you have to know what the end string should look something like
Field1 <>'Completed' And Field2 = 'SomeTextValue'


Unfortunately, your string will probably will not even compile because the string is not enclosed. So debugging will not help. Use single ' to wrap your strings that are inside double. If not instead of """ you actually will need """" and it becomes a mess.


"FieldName = 'SomeText'"
"fieldName = '" & SomeTextVariable & "'" resolves to
fieldName = 'SomeText'
or
"FieldName = " & SomeNumeric
FieldName = SomeNumeric

"fieldName = '" & SomeTextVariable & "' AND FieldName2 = '" SomeOtherTextVariable & "'"
"fieldName = '" & SomeTextVariable & "' AND FieldName2 = '" SomeOtherTextVariable & "'"

 

[MajP]

To add to what Doc_Man said. The original problem was in the format of your argument not in the syntax of the criteria.
Code:

DoCmd.OpenForm "MyForm", acFormDS, ,WHERE [Field1] Is Not Like "Completed" , , acNormal

l
The argument for the criteria needs to be a string. Yours will not resolve to a string so I doubt it would even compile.
Code:

WHERE [Field1] Is Not Like "Completed"

This is how you pass the string. Not sure that yours would even compile.
Code:

"WHERE [Field1] Is Not Like 'Completed'"

Just like you passed "MyForm" not MyForm

 

[isladogs]

To add to what Doc_Man said. The original problem was in the format of your argument not in the syntax of the criteria.
Code:

DoCmd.OpenForm "MyForm", acFormDS, ,WHERE [Field1] Is Not Like "Completed" , , acNormal

l
The argument for the criteria needs to be a string. Yours will not resolve to a string so I doubt it would even compile.
Code:

WHERE [Field1] Is Not Like "Completed"

This is how you pass the string. Not sure that yours would even compile.
Code:

"WHERE [Field1] Is Not Like 'Completed'"

Just like you passed "MyForm" not MyForm

@MajP
Its still not correct as the filter criteria should not include WHERE.
It also shouldn't have 'Is'

If using like then it should be

"Field1 Not Like 'Completed'"

However this is still better

"Field1<>'Completed'"

 

[MajP]

@Ridders
Thanks. Sorry, typing too quick in the little "quick reply" window and copy paste errors. Did not even notice the where. For some reason my browser will not let me use the Advanced editor where you can see what you are doing or let me go back and edit.

 

[isladogs]

@majp
No problem. I nearly missed the 'Is' error myself.
Have you checked to see whether you've selected the advanced editor option in your settings.

 

[MajP]

Actually it is computer and location specific. No problem editing from home desktop. I think it is a browser setting.

 

[isladogs]

Which browser?

 

[ECEK]

So anyhoo.....
This is my onupdate code which I'm sure just needs tweaking.

Dim strWhere As String
strWhere = "Field1 = """ & Me.MySelection & """" And "Field2 = """ & [Forms]![frmHidden]![User] & """"

Me.Filter = strWhere
Me.FilterOn = True
End Sub

Could somebody please help to tidy this up?
If I use

strWhere = "Field1 = """ & Me.MySelection & """"

Then it works.
When I use

strWhere = "Field2 = """ & [Forms]![frmHidden]![User] & """"

This also works.

I have just added the word AND between them. Obviuosly this doesn't work.

Thanks in advance.

 

[Minty]

You appear to have an extraneous " before Field2

Dim strWhere As String
strWhere = "Field1 = """ & Me.MySelection & """" And[COLOR="Red"] "[/COLOR]Field2 = """ & [Forms]![frmHidden]![User] & """"

' Or using single quotes     strWhere = "Field1 = '" & Me.MySelection & "' And Field2 = '" & [Forms]![frmHidden]![User] & "'"

Debug.print StrWhere

Me.Filter = strWhere
Me.FilterOn = True
End Sub

You can see how much easier it is to read with the single quotes.

 

[ECEK]

Minty....I want to have you babies !!!
Thank you sooooo much
Works a treat.