Re: [MS_AccessPros] VBA code to filter report question

 

Because I have the same report 8 times with the same source. the difference is only the criteria, I was trying to reduce the number of queries and reports. But I just realized at this point that I do not think that this solution will work. I have buttons that emails the reports after it is exported to a folder. shown below. I am seeing the wisdom it what you are saying. I will research the way to make that happen for 8 different sets of criteria tomorrow.

    DoCmd.OutputTo acOutputReport, "Attendance_Absence reportForPoly", "PDFFormat(*.pdf)", "\\Afwfs\AF\Services\UnivSrv\Admin Services\Management Support Services\ADMINISTRATIVE SERVICES DATABASES\MANAGEMENT SUPPORT SERVICES T-A-P\ABSENCE DATABASE\Department Reports\PolyTimesheets.pdf", False, "", 0, acExportQualityPrint

Jim Wagner

---

On Wed Jul 05 2017 14:29:15 GMT-0700 (US Mountain Standard Time), Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 

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: Jim Wagner <luvmymelody@yahoo.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

---

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

.

__,_._,___