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

[mr879]

Hello everyone I have been stuck on this problem for over 4 days now, I just cannot figure out how to write the code to do this. If you look at a table in access and you press the drop down menu for one of the fields such as "first name" there are options in number filters the specific one I am trying to recreate is the "does not equal" filter such as if you want to display anyone that does not have the first name "Jamie" as their first name you would use the "does not equal" filter. How would I go about doing this?

Note: I am accessing a table through SQL server using Access.


Here is an example table you can use for reference in this case I am trying to filter out anyone with the first name "Jamie" and anyone with the last name "Cartman"

Please look at the Logic that I have provided at the bottom of the table first and see if it is good to use (some of my syntax are incorrect)

First Nam	Last Nam
Jamie	Lanestor
Green	Arrow
Eric	Cartman
Homer	Simpson
Jamie	ruhl
Pernelli	Cartman

Here is what I was thinking of doing but having trouble referencing the column without actually referencing the field name itself.

'Using a for loop to go through all the values within the column "First Nam" but Len takes the length of the text it self not the column so that is an issue
for i= 1 to Len("[First Nam]")

'If the current value in the field "First Nam" does not equal "Jamie" (The syntax is wrong I believe) then....
if ("[First Nam]"(i)<> "Jamie") Then

'I do not know how to filter the data here and append them at the same time I was thinking of something like this clearly the syntax is wrong
Me.Filter="[First Nam] = " & "First Nam"(i) & """"
end if

' Now doing the same thing for the second Column by having a nested for loop so that nothing is out of scope
for j=1 to Len("[Last Nam]")

if ("[Last Nam]"(j) <> "Cartman") Then

'Attempting to append the filtered values so that i can save my previous filtered data without trying to overwrite them.
Me.Filter= Me.Filter & " AND Last Nam = """ & "Last Nam"(i) & """"
end if

next
Me.FilterOn= True
next

[June7]

Why not use a query? Try:

SELECT * FROM tablename WHERE FirstName <> "Jamie" AND LastName IN(SELECT LastName From tablename WHERE LastName<>"Cartman");

[mr879]

I do not have any experience using queries so I am unfamiliar with them so I am trying to figure out a way to use the table instead.

This code does not work for me says expected case and highlights the astrick.

[June7]

Access makes using queries so easy. There is a builder tool and wizards to help.

The query works for me. Did you use the query builder to construct query then switch to SQL View to see structure? Did you use your actual table and field names? The wildcard should work but instead can explicitly reference field names.

Your criteria requires each record to consider value in other records. This usually requires a subquery as shown in my example or domain aggregate function.

WAIT: I just realized this does not require subquery. This query also works:

SELECT FirstName, LastName FROM Tablename WHERE FirstName<>"Jamie" AND LastName<>"Cartman";

So your VBA can be simply (no looping necessary):

Me.Filter = "FirstName<>'Jamie' AND Lastname<>'Cartman'"

[mr879]

I have pasted the SELECT FirstName, LastName FROM Tablename WHERE FirstName<>"Jamie" AND LastName<>"Cartman";

you have provided in the new query which i created of the Table and then in SQL view I have placed that code and saved it and then in my split form where my UI is I have incorporated Me.Filter = "FirstName<>'Jamie' AND Lastname<>'Cartman'" in to one of the button actions.

It does not Filter any data unless I have to reference the query file? Since part of the code is inside it.

[June7]

The query statement was just an example.

Bind form to table or query but do not use filter criteria.

Use the code to apply criteria to the Filter property of form.

The example code has static parameters. If you want them to be dynamic, that is another issue.

[mr879]

If I did manage to bind the form to the table how will I be able to use this code SELECT FirstName, LastName FROM Tablename WHERE FirstName<>"Jamie" AND LastName<>"Cartman";

Isn't that code SQL? So wouldnt you need a query to have in SQL view to use the code?

[June7]

Yes, that is SQL. Yes, use query builder to construct SQL. Yes, switch to SQL View to see the SQL statement.

A form or report RecordSource property can be set to a table object, a query object, or an SQL statement.

As I said, I posted that SQL only as information. You don't have to use that exact statement anywhere. Or it can be the RecordSource of the form, in which case the VBA code is not needed because the static parameters are already applied in the SQL.

Again, if you want dynamic parameters, that is another issue.

[mr879]

I do not understand why a query would be useful in this case, since the the table is accessed through an SQL server using access so if the data were to change that means I would have to update the query to unless it automaticaly does it. If it does not seems like it is more work for a single task.

[June7]

Queries automatically refresh when they are opened or when a form or report using the query is opened.

[mr879]

When you say bind a form and table isn't that technically what a split form is? If so that is the setup I already have.

[June7]

A split form is a form and I expect it would be bound. Again, binding a form is done by setting the RecordSource property to a table, query, or SQL statement. Your choice.

[mr879]

I thought you need a query to make an SQL statement is there somewhere else I can write the SQL statement so that I can bind the form to the SQL statement and that way I can use the SQL statement you have provided.

[June7]

Directly in the RecordSource property. Can type the SQL statement or popup the query builder by clicking the ellipsis (...).

This can also be done in the RowSource property of combo and list boxes.

[mr879]

Okay I will give it a try when I get home I'll post back if the issue is fixed or if there was a problem.