Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Multiple criteria in Access Report

Hi All.

Creating a report in Access 2007 for the boss.

I have a field for the type of event that I need to filter out "DOC APPT"  and "Meeting".
I tried typing in the criteria line both of the above in quotes with "or" in between NOT in quotes.
I tried typing each on a separate line.
Either way it filters out the first but not the second.

I cannot believe this is so damn hard!!
I hate Access!

Any thoughts?
Thanks
B
0
ButchDog
Asked:
ButchDog
3 Solutions
 
Dale FyeCommented:
WHERE [Event Type] NOT IN("Doc Appt", "Meeting")

or an alternative might be:

WHERE [Event Type] <> "Doc Appt" AND [Event Type] <> "Meeting"
0
 
ButchDogAuthor Commented:
Ooops.
I screwed up.
I meant Query NOT report.
I'm working in Query view.
Duh!!

Does that change anything?
0
 
mbizupCommented:
In VBA, remove any existing EventType creteria from your report.

You can then open it, filtered to the data you need like this (code behind an Open Report command button):

Docmd.OpenReport "rptYourReport", acViewPreview,, "[EventType] <> 'Doc Appt' and [EventType] <> 'Meeting'"
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:


Private Sub Report_Open (Cancel As Integer)
   Me.Filter = "[YourEventTypeFieldName] = "  & " Not ( " & Chr(34) & "DOC APPT" & Chr(34)  & " OR " & Chr(34) & "Meeting" & Chr(34) & " )"

   Me.FilterOn = True

End Sub

Remove any criteria you may have in the query.
0
 
mbizupCommented:
<Does that change anything? >

You should generally avoid giving users direct access to your data through tables or queries, but rather use forms for data entry and reports for reporting data (this give you a lot more control).

The criteria fyed posted will work in a query.
0
 
ButchDogAuthor Commented:
Yes, I will design a report after I get the query working.

This really has to be done in VBA?
You'd think it would be simple enough to do right in the query design view.

Did I mention that I hate Access?

Thanks
I'll give your suggestions a try.

B
0
 
mbizupCommented:
If you are entering this directly in the Criteria box in your query builder, use this:

NOT "Doc Appt" AND Not "Meeting"

(But I'd suggest putting this query behind a form or report if you are not doing that already, rather than opening it to general users)
0
 
mbizupCommented:
<Did I mention that I hate Access?>

You'll learn to love it :)

0
 
ButchDogAuthor Commented:
Got it!

Thanks to ALL!
:)

B
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now