Can I filter or set criteria in Reports?

[undefeatedskillz26]

2 Questions

I have query named Bills that has about 200 records. I need to make a report so I put a criteria in the query for the columns LastName and wrote:

<>"Johnson"

or

NOT "Johnson"

1.) Do both output the same thing? I want to have all records in the query and take out any record with last name Johnson.

Now in the query it shows me all the records besides Johnson. Now I have 100 records. Great. Okay. But now I must have forms that shows all records including Johnson.

2.) How can I take out cirteria on Query and add a criteria or filter it in the columns LastName in Reports? Can I write some kind of criteria in Column's control source? Anyone know how? In control source it says "LastName" right now.

Would like to set filter to only reports so I can get the 100 filetered records and have query show the 200 records. Is this possible? I am sure just do not know ehre to go.

Many thanks my friends.

 

[pbaldy]

You can take the criteria out of the query and use this to filter the report:

http://www.baldyweb.com/wherecondition.htm

 

[undefeatedskillz26]

You can take the criteria out of the query and use this to filter the report:

http://www.baldyweb.com/wherecondition.htm

Okay so in Event Procedure I write:

DoCmd.OpenReport "Bills", , , "LastName = '" & Me.NOT "Johnson" & "'"

Do I need to type in End Sub or anything else? Thanks.

Name of Report = Bills
Name of Column that has the Criteria = LastName
Criteria Needed = NOT "Johnson" (to include all records expect records with LastName Johnson)

 

[essaytee]

Okay so in Event Procedure I write:

DoCmd.OpenReport "Bills", , , "LastName = '" & Me.NOT "Johnson" & "'"

Do I need to type in End Sub or anything else? Thanks.

Name of Report = Bills
Name of Column that has the Criteria = LastName
Criteria Needed = NOT "Johnson" (to include all records expect records with LastName Johnson)

Try
LastName <>

 

[undefeatedskillz26]

Okay so I typed the following code into On Click Event Procedure:

Private Sub CtlLastName_Click()
DoCmd.OpenReport "rptBills", , , "LastName = '" & Me.<> "Johnson" & "'"
End Sub

When I did that it seems that it does nothing. The report still returns all 200 records. When I go back into the code:

DoCmd.OpenReport "rptBills", , , "LastName = '" & Me.<> "Johnson" & "'"

is in red font. I am thinking the code may not be exactly right. Does anyone have any suggestions?

 

[pbaldy]

More like:

DoCmd.OpenReport "rptBills", , , "LastName <> 'Johnson'"

Or to use a textbox:

DoCmd.OpenReport "rptBills", , , "LastName <> '" & Me.TextboxName & "'"

 

[undefeatedskillz26]

More like:

DoCmd.OpenReport "rptBills", , , "LastName <> 'Johnson'"

Or to use a textbox:

DoCmd.OpenReport "rptBills", , , "LastName <> '" & Me.TextboxName & "'"

Okay so I typed in the code and now the code is not in red font which makes me believe that we are taking a step forward.

The problem now is that it still does not filter any records. The same 200 records with Johnson are being shown.

I would think that there would be some type of error shown so I could figure it out.

Private Sub CtlLastName_Click()
DoCmd.OpenReport "rptBills", , , "LastName <> 'Johnson'"
End Sub

 

[pbaldy]

It could be that there's a space after the name? Try

DoCmd.OpenReport "rptBills", , , "LastName Not Like 'Johnson*'"

If that doesn't work, can you post the db here?

 

[undefeatedskillz26]

It could be that there's a space after the name? Try

DoCmd.OpenReport "rptBills", , , "LastName Not Like 'Johnson*'"

If that doesn't work, can you post the db here?

I can but I will have to do it later because I have to gut everything out. There is very confidential information on there and the database has my Companies name, etc. I would love to post here just need some time later to gut it if I don;t figure it out.

Can I ask what do the 3 commas represent? Is each comma a column and so since there are 3 we are now working on the fouth column? Just trying to weed out mistakes.

In the pic attached the WhereCondition is bolded? I saw this when I was typing the code. Does that mean WhereCondition is where the error remains? If so I tried <>, NOT, and NOT LIKE

Edit - I tried adding 6 commas since the 7th is the one I am working on but it didn't work.

Thanks.

 

[pbaldy]

The bold tells you which argument you're working on. The commas separate the arguments, so changing the number changes which argument.

 

[pbaldy]

Looks like a different issue. You can't open the report from within the report; it's already open. You could filter it from there:

Me.Filter = "[16Weeks] <> 'Over 16 Weeks'"
Me.FilterOn = True

Presumably you'd want to add something to toggle it back and forth.

 

[essaytee]

Okay attached is a sample database. Hopefully I gutted everything out. If you press F11 and go into reports in the column 16weeks is where I put the code.

I am sure it is a real simple fix like a mispelling knowing my luck. If you have a chance can you let me know how to filter the report so that all records show besides the one that say "Over 16 Weeks"

Thank you, thank you and thank you my friend.

Create a form, add a command button. Add following to the click event of the command button.

 Private Sub cmdReport_Click()
    
    ' either one of the following, note the [] around 16Weeks and the use of either = or <>
    
    'DoCmd.OpenReport "rpt16WeeksNotice", acViewPreview, , "[16Weeks] = 'Over 16 Weeks'"
    [COLOR="Blue"][B]DoCmd.OpenReport "rpt16WeeksNotice", acViewPreview, , "[16Weeks] <> 'Over 16 Weeks'"[/B][/COLOR]
    
End Sub

I think some of your issues were surrounding the name of your field, "16Weeks". Without the square brackets surrounding the field name errors were generated. It would be advisable to not use numbers at the start of field names. "Week16" would be a better choice.

I've attached the db for your convenience. Run the form, click the Report button and the report will open in preview mode only showing NOT 16 weeks records.

 

[essaytee]

Looks like a different issue. You can't open the report from within the report; it's already open.
...snip...

That's what I thought initially, where is the form? Where is Johnson?

 

[undefeatedskillz26]

]
Presumably you'd want to add something to toggle it back and forth.

What does this mean?

 

[undefeatedskillz26]

Big thank you to Pbaldy and essaytee. Both have you have been very helpful. I will test these options out when I have some free time and I am back on my computer. I just wanted to say thank you for the replies, you guys are great!!!!

 

[pbaldy]

After you click in the textbox to filter it, do you want to be able to unfilter it?

 

[undefeatedskillz26]

After you click in the textbox to filter it, do you want to be able to unfilter it?

I see what you are saying. No when I click the botton on the form I will only need the report when it's filtered. When clicking the button it opens 3 other reports as well.

In the form while viewing each person it will show if they are over or under the weeks.

This is why I needed the query to show all the fields so in the forms I can see all the records but in the report I can only see the filtered ones.

Thanks guys!!!! Definitely made my day.

 

[essaytee]

Okay I will go this route if I create a form. If I do I can type in the code like pbaldy said and filter it at the report or do it on the form when I clcik the button like you suggest, correct?

You have many options, the options provided thus far get you started. Whatever reports you design and create the users should only be able to run them via the forms you create, not via the database window.

So opening a report from a form opens up many options as regards filtering. The report will be able to access any data from the open form.

The example I provided, is effectively, a hard-coded example. The Where clause of the DoCmd.OpenReport command can be reduced to a string variable. Prior to running the report, you will, in time, prepare the string variable according to various criteria, any criteria (any field name, any value). This is all done from the form.

The queries you create can also be filtered from the data contained on forms and used as recordsources for reports.

As I said, there are many options.

 

[undefeatedskillz26]

You have many options, the options provided thus far get you started. Whatever reports you design and create the users should only be able to run them via the forms you create, not via the database window.

So opening a report from a form opens up many options as regards filtering. The report will be able to access any data from the open form.

The example I provided, is effectively, a hard-coded example. The Where clause of the DoCmd.OpenReport command can be reduced to a string variable. Prior to running the report, you will, in time, prepare the string variable according to various criteria, any criteria (any field name, any value). This is all done from the form.

The queries you create can also be filtered from the data contained on forms and used as recordsources for reports.

As I said, there are many options.

Yes you are exactly right. The report is opened thru a form, from that point many options are available. Thank you again for taking the time to reply. All of this has been very informative.

 

[undefeatedskillz26]

Looks like a different issue. You can't open the report from within the report; it's already open. You could filter it from there:

Me.Filter = "[16Weeks] <> 'Over 16 Weeks'"
Me.FilterOn = True

Presumably you'd want to add something to toggle it back and forth.

Okay so I finally was able to add the code in the report:

I still get all results.

Did you get it to work?

Am I doing something wrong?