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 WagnerOn 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: Shay Holmes <shaybellaholmes@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar