Re: [MS_AccessPros] VBA code to filter report question

 

Jim,

I agree with John regarding the Not Like without a wildcard.

My concern is that you are storing data in code. I would expect there is a good reason for your selections but what happens when you want to exclude D0844? Are you going to change your code? I would strongly urge you to create a table with the Dept ID and some type of attribute storing whether it is or is not included in a list. Maintain data, not code.

Regards,

Duane Hookom

(Back in USA!)

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, July 5, 2017 1:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] VBA code to filter report question

 

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: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

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

.

__,_._,___