Recreating the Does Not Equal number Filter option in access using vba

[mr879]

Hey I have a question about the SQL in the Query, when I pasted the SQL code in the query that I created and ran the code it prompted me for First Nam and Last Name, Why would it do that I thought the statement only selected all the data except for Jamie and Cartman from FirstNam And LastName




SELECT [FirstNam], [LastNam]
FROM Table1
WHERE ((([FirstNam])<>'Jamie') AND (([LastNam])<>'Cartman'));

[June7]

It isn't finding the field names. Make sure the spellings are correct, including spaces.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[mr879]

Cool the filtering works in the query now it was because no space was provided in the field names in the code. I just have to now incorporate it in to the Vba so the filtering is applied to the table itself, I found some information online that showed that using the DoCmd.RunSQL will work but another person replied to my post saying it is because I do not have a action query setup so it will not work.

Here is the link to the source http://www.fontstuff.com/access/acctut15.htm



Dim strSQL As String
strSQL = "SELECT Table1.[First Nam], Table1.[Last Nam]" & _
"FROM Table1 " & _
"WHERE ((([First Nam]) <> 'Jamie') AND (([Last Nam]) <> 'Cartman'));"
DoCmd.RunSQL strSQL
Me.Filter = ""
Me.Filter = "First Nam<>'Jamie' AND Last Nam<>'Cartman'"
Me.FilterOn = True

[June7]

You still don't understand.

The SQL must be the RecordSource of form. If the SQL includes the filter criteria, VBA is not needed.

[mr879]

Yes I know what you mean I would have to go in to design view in form and change the record source to my query file where the SQL is written, but I cannot do that alot of my my code relies on the table itself as the record source and if I changed the record source of this to the query then that means my split form will not be useful because at the bottom of the split form is the table so once the data is filtered the filtered data will be shown in the query. So I am trying to apply it on the table itself instead of the query.

[June7]

If the RecordSource is table, then there is no need for the SQL string in the VBA. There is no need for the RunSQL, which does not work with SELECT anyway. Just set the FilterOn property of the form.

And because field names have spaces, must be enclosed in brackets:


Me.Filter = "[First Nam]<>'Jamie' AND [Last Nam]<>'Cartman'"

[mr879]

That is the very first thing I tried but had a syntax error lol... I forgot that I needed the square brackets when there are spaces in the field name. All that work for a simple mistake now I feel stupid lol.

This is the solution,


Me.Filter = ""
Me.Filter = "[First Nam]<>'Jamie' AND [Last Nam]<>'Cartman'"
Me.FilterOn = True


Thank You for your help!!!!!!

[June7]

Which is why experienced developers avoid them.