Re: [MS_AccessPros] VBA code to filter report question

 

Jim-

Because you're not using any wild cards in your NOT LIKE predicates, they're the same as <>.  So, you can do:

[EmplStat SD] <> "Terminate"

AND [Dept ID] NOT IN ( .... your list of comparison values here ...)

AND [Paygrp CD] <> "STU"

AND [Flsa Stat Cd] = "N"

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Jul 5, 2017, at 20:35, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hello all,

I have 8 reports that have the same query source but different criteria. right now there are 8 queries for each of the 8 reports. So I decided to write some code to filter the report. My first code works perfectly. But I would like to see if there is a way to write it more efficiently. Like using an In() function. my first attempt that works is below.

'DoCmd.OpenReport "Copy Of Attendance_Absence report", acViewPreview, , "[Empl Stat Sd] Not Like 'Terminated'" & _
'" And [Dept Id] Not Like 'D0836'" & _
'" And [Dept Id] Not Like 'D0837'" & _
'" And [Dept Id] Not Like 'D0834'" & _
'" And [Dept Id] Not Like 'D0803'" & _
'" And [Dept Id] Not Like 'D0816'" & _
'" And [Paygrp Cd] Not Like 'STU'" & _
'" And [Flsa Stat Cd] Like 'N'"

But my attempt to make it more efficiently failed. I tried an InStr, an In() and even the below but none of them worked. Could someone give me a little help on the syntax. I am getting a 3075 error for a syntax error for missing operator.

DoCmd.OpenReport "Copy Of Attendance_Absence report", acViewPreview, , "[Empl Stat Sd] Not Like 'Terminated'" & _
" And [Dept Id] Not Like 'D0836' And Not Like 'D0837' And Not Like  'D0834' And Not Like  'D0803' And Not Like  'D0816'" & _
" And [Paygrp Cd] Not Like 'STU'" & _
" And [Flsa Stat Cd] Like 'N'"

Thank You

Jim Wagner

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___