Filters

[freddie1437]

I was wondering how I can go about inserting code to apply filter's to a form rather than using the filter by form on the form itself.

Thank you

[NeoPa]

When using the DoCmd.OpenForm function, the fourth parameter is WhereCondition.

Check out the full details in Help.

[ADezii]

It is actually a 2-Step process:

1. Setting the Filter Property of the Form:
   

   Me.Filter = "[LastName] ='Fuller'"

2. Setting the FilterOn Property of the Form in order to apply the Filter:
   

   Me.FilterOn = True

3. Combined, it would be something similar to:
   

   Me.Filter = "[LastName] ='Fuller'"
   Me.FilterOn = True

[NeoPa]

This raises the point of when you want to apply the filter.

If you want to open the form with a pre-prepared filter applied then my earlier response is what you need.

If, however, you want, from within the code of the form itself, to change the filter dynamically, then ADezii's response is what you require.

Now you have both anyway - untold riches ;)

[freddie1437]

Thank you guys! Well what I was trying to accomplish was exporting a form to excel, but only capturing data that is greater than 0. It works when I manually hit filter by form then apply filter, but I want it to work when I click the button.

[NeoPa]

Freddie, if what you've posted so far hasn't convinced you already, you need to be much clearer in what you post.

It's not about what you understand, it's about how anyone can understand what you post. Remember we don't know anything about your situation except what you tell us. You're not telling us very much I'm afraid.

Reading between the lines (with a little guesswork thrown in for good measure), I suspect you are after what ADezii was suggesting.

If you need more specific help you will HAVE to start presenting your situation a lot more clearly.

[freddie1437]

Sorry about that, what I want is to export a form that has different months on a form. For example if we are in January I only want to export January's numbers, but I have a list of people in January so I only want to export the people that have a number that is greater than 0. I am able to grab only the month I am looking for to export by Formatting NOW by month to match the current month. My problem is that when I export, it's exporting everything. So I wanted to run something that would eleviate the problem.
Thank you

[NeoPa]

I'm sorry I didn't pick up on your earlier post before where you said you want to export a form. I was so confused by the fact that nothing in the post seemed to make any recognisable sense, that I missed this fundamental point.

Queries and tables can be exported, not forms or reports (as far as I'm aware).

Unfortunately, your latest post still talks in terms of selecting "people" whose "numbers" are greater than 0.

People don't live in databases, and very many things in a database are numbers!

If you have a table that somehow tracks people in your database, and that has a numeric field that you need to compare, then for goodness sake how do you expect me to know about any of that unless you tell me. Properly. With the level of detail I will need to be able to answer at least the simplest of questions.

I don't know how I can get this across to you. I seem to have been telling you a number of times without any noticeable effect.

[NeoPa]

As a further point, you give no indication that you have even tried to implement ADezii's answer to your question.

You do understand that you are expected to try this for yourself, or explain why, for you, this is not possible.

[freddie1437]

I'm sorry I didn't pick up on your earlier post before where you said you want to export a form. I was so confused by the fact that nothing in the post seemed to make any recognisable sense, that I missed this fundamental point.

Queries and tables can be exported, not forms or reports (as far as I'm aware).

Unfortunately, your latest post still talks in terms of selecting "people" whose "numbers" are greater than 0.

People don't live in databases, and very many things in a database are numbers!

If you have a table that somehow tracks people in your database, and that has a numeric field that you need to compare, then for goodness sake how do you expect me to know about any of that unless you tell me. Properly. With the level of detail I will need to be able to answer at least the simplest of questions.

I don't know how I can get this across to you. I seem to have been telling you a number of times without any noticeable effect.

Okay, this is the problem and yes you can export a form. I have DATA in a table which I would want to only export a certain criteria of that the only problem I am having is that grabbing that data through queries. I have a table that has months and Employee information. I don't know how to grab the information through queries because I only want to grab the current month, so I decided to add a button to only make the current month visible so that when I export it to excel, it will only show this month, I have figured that part out on my form using code, however I want only the current month that has a number greater than 0. I used the code:

.
    If [Text40] = "September" Then [January].Visible = False
    If [Text40] = "September" Then [February].Visible = False
    If [Text40] = "September" Then [March].Visible = False
    If [Text40] = "September" Then [April].Visible = False
    If [Text40] = "September" Then [May].Visible = False
    If [Text40] = "September" Then [June].Visible = False
    If [Text40] = "September" Then [July].Visible = False
    If [Text40] = "September" Then [August].Visible = False
    If [Text40] = "September" Then [October].Visible = False
    If [Text40] = "September" Then [November].Visible = False
    If [Text40] = "September" Then [December].Visible = False

Now Text40 is using : =(Format(Date(),"mmmm")

[freddie1437]

I tried ADezii's and it didn't work, It came with an error message saying "Run-Time Error 2001: You canceled the previous operation. and I don't know why it wont work. Yes I understand that I have to try it and I did, I'm sorry it took so long to explain why it didn't work.

[NeoPa]

Okay, this is the problem and yes you can export a form....

It seems you can export a form. It appears that the options associated with this approach are somewhat limited though. Maybe they expand as you get more familiar with it. Who knows. It's also possible to "Publish" a form (to other MS Office applications). This would include some level of formatting.

... I have DATA in a table which I would want to only export a certain criteria of that the only problem I am having is that grabbing that data through queries. ...

??? Not sure what this means.

...
I have a table that has months and Employee information. I don't know how to grab the information through queries because I only want to grab the current month, so I decided to add a button to only make the current month visible so that when I export it to excel, it will only show this month, I have figured that part out on my form using code, however I want only the current month that has a number greater than 0. I used the code:

.
    If [Text40] = "September" Then [January].Visible = False
    If [Text40] = "September" Then [February].Visible = False
    If [Text40] = "September" Then [March].Visible = False
    If [Text40] = "September" Then [April].Visible = False
    If [Text40] = "September" Then [May].Visible = False
    If [Text40] = "September" Then [June].Visible = False
    If [Text40] = "September" Then [July].Visible = False
    If [Text40] = "September" Then [August].Visible = False
    If [Text40] = "September" Then [October].Visible = False
    If [Text40] = "September" Then [November].Visible = False
    If [Text40] = "September" Then [December].Visible = False

Now Text40 is using : =Format(Date(),"mmmm")

I can't find any references here to fields where your data is stored (Not controls on the form but fields in the underlying recordset) :S
I get that Me.Text40 has a string value of the current month, but this is a form control. It's being compared with the literal value "September". I can't see why "September" is special in any way. I find your code to be so unusual that I can read very little from it.
Otherwise, I still have nothing to work from. No field names and the undefined "number" I commented on earlier is still there. Just as devoid of meaning as it was before.

I've been racking my brains to find a way we can go forward on this that will make sense to both of us.

I'm horribly afraid (from looking at your posted code) that what you want may not be possible with your current data structure. Can you confirm that in the table (or query) that the form is bound to, you have fields for each month of the year? If so I think we may have a fundamental problem.

[NeoPa]

I tried ADezii's and it didn't work, It came with an error message saying "Run-Time Error 2001: You canceled the previous operation. and I don't know why it wont work. Yes I understand that I have to try it and I did, I'm sorry it took so long to explain why it didn't work.

If what I am coming to suspect is true, then getting that to work is likely to prove very difficult indeed, even for an experienced developer.

What I suggest you do at this stage (read and respond to earlier post first of course) is to post the table metadata for the table the form is bound to.

Just list all the fields by name and include their type (String; Numeric; Date/Time; etc).

We may find that the way the data is structured is not conducive to filtering in the way you require. That would be bad news indeed, but let's see what we have before we reach that conclusion.

PS. That error message is particularly unhelpful and has confused developers with much more experience than you have. Access can be like that sometimes.

[freddie1437]

It seems you can export a form. It appears that the options associated with this approach are somewhat limited though. Maybe they expand as you get more familiar with it. Who knows. It's also possible to "Publish" a form (to other MS Office applications). This would include some level of formatting.

??? Not sure what this means.

I can't find any references here to fields where your data is stored (Not controls on the form but fields in the underlying recordset) :S
I get that Me.Text40 has a string value of the current month, but this is a form control. It's being compared with the literal value "September". I can't see why "September" is special in any way. I find your code to be so unusual that I can read very little from it.
Otherwise, I still have nothing to work from. No field names and the undefined "number" I commented on earlier is still there. Just as devoid of meaning as it was before.

I've been racking my brains to find a way we can go forward on this that will make sense to both of us.

I'm horribly afraid (from looking at your posted code) that what you want may not be possible with your current data structure. Can you confirm that in the table (or query) that the form is bound to, you have fields for each month of the year? If so I think we may have a fundamental problem.

That's what I was afraid of, I was trying to make this possible for someone else. It's their database not mine and their structure was wrong compared to how I would have made it and I don't think it's obtainable. I think I may have to write a new database for this person.

A question I do have which I haven't been able to figure out and I hope you may be able to, I have attendance records for each employee (AttendanceID Autonumber) and it has a series of possibilities such as points, sick days, etc. All of which contain certain points depending on the situation for example if an employee used 1 sick day they would receive 2 points. I made a table called Reason Table which has all the different criteria. My question is if an employee received points in any month they would not be eligable for perfect attendance and that's my problem. How can I get Access to exclude an employee through their employeeID number where they didn't receive an infraction that would have given them points or affected their perfect attendance? I am using Access 2003. Thank you.

[NeoPa]

Freddie, it's probably better if you ask this question in a separate thread. Post a link to your thread in here by all means and I'll pop over and have a look.

You will, of course, need to specify the question more clearly and provide some detail to help someone understand what it's about.

The layout of the tables would help greatly to understand the problem.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[freddie1437]

I will post a new question, Thank you NeoPa

[NeoPa]

No worries :)

Post a link in here so I can find it easily.

[freddie1437]

New Table?

Here you go, Thanks again!