Reports :: Nested IIF Statements With 2 Criteria To Meet
Dec 2, 2014
I have a report based on a query and in that report I added a text box to be yes or no depending on 1) the number of days to complete and 2) the type of urgency. There's 3 different urgency levels so I was trying to create iif statements based on each one with the corresponding days using the OR operator between them like below:
=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes","No")_
Or IIf([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)","Yes","No")_
Or IIf([t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")
Any 1 of those by themselves works fine but I need it based on all of that. So with that said, is there a way to do this? I tried using VBA code, but I think with the type of report I'm running that that wont work. It has a repetitive one line detail section with the data I'm trying to view.
I have a list of projects and I need to display their status (Red or Green) in a text box.
My fields are [PercentComplete],[StartDate],[EndDate],[ReportDate]
There are two ways a project could have a red status.
1. [PercentComplete] < 100 AND [ReportDate] < [EndDate] OR 2. [PercentComplete]<100 AND IsNull([EndDate])
There is one way it could have a green status: [PercentComplete] = 100 AND Not IsNull([StartDate])
This is the best I could come up with for the Iif statement, but I get "invalid argument" which I suspect relates to the AND portion.
Code: Iif([PercentComplete] < 100 AND [ReportDate] < [EndDate], "Red", [PercentComplete]<100 AND IsNull([EndDate]),"Red", [PercentComplete] = 100 AND Not IsNull([StartDate]),"Green","")))
SELECT tblNewTrans.Buyer, Count(tblNewTrans.Buyer) AS ["Total New Leads"], (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.new=true) AS Expr1, (select count(tblnewtrans.buyer) from tblnewtrans where tblnewtrans.refund=true) AS Expr2 FROM tblNewTrans GROUP BY tblNewTrans.Buyer;
can anyone tell me why statement is working correctley. i have a table with a company field, paid field, refund field.
i need to run a query that gives me an amount to records for each company(that bit works), but also tell me how many payments have been recieve(new field) and how many payments have been refunded. all grouped under the correct company name. please help!!!!
I am trying to combine the below queries into one using nested Select statements, but I am not having any luck. Any examples on how to do this would be appreciated. Thanks
qry1000_006_01-Base4 syntax:
SELECT RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP, RIVMTRM0_STDY_GRP_TST_REMARK.IVMTESTSN FROM RIVMTRM0_STDY_GRP_TST_REMARK GROUP BY RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP, RIVMTRM0_STDY_GRP_TST_REMARK.IVMTESTSN HAVING (((Count([IVMSGRP])<>Max([IVMRMKSN]))=-1));
SELECT [qry1000_006_01-Base4].IVMSGRP, [qry1000_006_01-Base4].IVMTESTSN, RIVMTRM0 _STDY_GRP_TST_REMARK.IVMRMKSN, RIVMTRM0_STDY_GRP_TST_REMARK.IVMRMK FROM [qry1000_006_01-Base4] INNER JOIN RIVMTRM0_STDY_GRP_TST_REMARK ON ([qry1000_006_01-Base4].IVMSGRP = RIVMTRM0_STDY_GRP_TST_REMARK.IVMSGRP) AND ([qry1000_006_01-Base4].IVMTESTSN = RIVMTRM0 _STDY_GRP_TST_REMARK.IVMTESTSN);
How do you write complex nested IF THEN ELSE END IF statements..How do you know what IF statement to put first?Then how would you know where to put ELSE, or END IF?Before you start with the VBA, what are the foundations for making it work successfully, know where you are, know where to put END IF etc?
I've seen many times when using VBA people set up an initial foundation for writing code. For example when needing to put something in quotes, they do "" then enter the text in between, so they don't get errors on running (A very simple, crude example I know, but is there a similar basis for nested IFs).
Hello all I was wondering if there is limitation to the number of IIf statements that can be nested in a calculated field. Basically I need to assign a particular ‘label code’ to a record that will be derived from data with in several fields within the query. The data from the table (that the query is derived from) will be completely deleted and recreated on occasions, meaning I can not write data back to the table as it would be lost when the re-creation happens. There is a possibility of many derivations for the ‘label code’ (maybe up to 100) so the nested IIf code will be substantial. If there is a better way to do what I want I am all ears however I am not a VB programmer so would rather stick to doing within the query design grid, or SQL at a stretch. Anyway the main focus to the question is there a limitation to the number of nested IIF statements.
I am trying to get the output of several nested IIF expressions to show in only one filed in the table created. The output needs to be captured in a table. I am staring with create table to get it to work before I go ahead and use the sql to update the original table (the table the data is from)
I can get each to run fine independently and the whole thing runs too, the issue is it wants to put each expression in a new column, thus it is forcing me to label the expression.
Can I change this to only output in one column or add some more sql to do so?
Here is the SQL (table and filed names have been changed to protect the innocent)
BTW, I cannot change the Table the data comes from, I am stuck with the single table structure (BOO).
************************************************** ************* SELECT TABLE1.ID, IIf([TABLE1]![STAGE]="D", IIf([TABLE1]![PROJ_ACTUAL_D_DATE] Is Not Null,"Tier 1", IIf([TABLE1]![APPROVAL_D]="Y","Tier 2",
SELECT DISTINCT tblvisit.[Pen ID] FROM tblvisit WHERE (((tblvisit.[Visit Date From])<=[forms]![fRMBOOKINGAVAILABILITY]![From]) AND ((tblvisit.[Visit Date To])>=[forms]![FRMBOOKINGAVAILABILITY]![To])) OR (((tblvisit.[Visit Date From]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to])) OR (((tblvisit.[Visit Date To]) Between [forms]![FRMBOOKINGAVAILABILITY]![From] And [forms]![FRMBOOKINGAVAILABILITY]![to]));
This successfully finds booked pens in a cattery. How though, can I get to see the pens that are free? Is there a feature that will allow me to conduct this query before conudcting another one to show the values which didn't appear? Thanks:D
I have a table called estimates and a field in that report is called RFA and this is a checkbox. How can i open a report i created (called EReport) for all rhe records in this table that have the RFA field checked (therefore true).
I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code: [WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code: [WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code: [WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup FROM T_STUDENTS, T_COACHING WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING)) GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;
It runs fine, untill the moment the count is 0. At that point it breaks the form. How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.
:confused: Hi I need help inserting IIF statements as criterias in my query.
Here's the query I have created from a table called XML_EXPORT which consists of a list of ORGANIZATIONS with various fields. What I'm trying to do is display: -the name of the EXEC_NAME_1 (primary executive's name) -the ADDRESS -the CITY -the POSTAL_CODE -the PROVINCE -the PUBLICATION -the ORG_LEVEL_1 (the organization's main name) -the ORG_LEVEL_2 (the organization's departments 2 to 5) -the ORG_LEVEL_3 -the ORG_LEVEL_4 -the ORG_LEVEL_5
Now not every record contains data in ORG_LEVEL_2 to ORG_LEVEL_5 so we want to display only ORG_LEVEL_1 and the last ORG_LEVEL that is not blank.
i.e. If a record has ORG_LEVEL_4 and ORG_LEVEL_5 blank then we want to only display: ORG_LEVEL_1 and ORG_LEVEL_3 (even though there is something in ORG_LEVEL_2)
Here's my query:
------------------------------------------ SELECT XML_EXPORT.EXEC_NAME_1, XML_EXPORT.ADDRESS, XML_EXPORT.CITY, XML_EXPORT.POSTAL_CODE, XML_EXPORT.PROVINCE, XML_EXPORT.PUBLICATION, XML_EXPORT.ORG_LEVEL_1, XML_EXPORT.ORG_LEVEL_2, XML_EXPORT.ORG_LEVEL_3, XML_EXPORT.ORG_LEVEL_4, XML_EXPORT.ORG_LEVEL_5 FROM XML_EXPORT WHERE (((XML_EXPORT.ADDRESS) Is Not Null)); ------------------------------------------
Can anyone show me how the Iif statement syntax would be ? Allan
I have a report with a nested Subreport. Main Report>Subreport1>Subreport2, for simplicity.
I have master/child set up for both Subreports, but I would also like to filter the Subreport2 records with a date range parameter.
Is there a way to get the Subreport2 where clause to recognize the Subreport1 control I want to use for the date range?
So far I have tried:
Between Int([Reports]![Processing_Performance_Req_Dataset]![report]![QC_Static_Processing_Subreport]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![report]![QC_Static_Processing_Subreport]![Collection_Date])+0.9)
Between Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport]![Collection_Date])+0.9)
and
Between Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport].[Report]![Collection_Date]) And (Int([Reports]![Processing_Performance_Req_Dataset]![QC_Static_Processing_Subreport].[Report]![Collection_Date])+0.9)
where Processing_Performance_Req_Dataset = Main Report QC_Static_Processing_Subreport = Subreport1 and Collection_Date = textbox control on Subreport2.
Is there a syntax error? I have other, single level, Subreports on the main report with a similar data range parameter that work just fine. Or does this type of parameter input not work on second level Subreports? Every time I run the main report it asks me for a parameter but doesn't accept my input, and Subreport2 doesn't load any records.
I am creating a report based off a query. My problem is that I am trying to create conditional statements within a unbounded text box. I would like to say: =[Field] where [Field2] = X. Unfortunately, I can not find any support on this matter.
I have a report that is based on nested (I think thats the phrase) query's.
Complicated Query based on another query (so I can't see a way to get at the the source SQL to change or use elsewhere)
This gives a list of say 20 records I generally want printed. I use the exact same query criteria with a separate update query to add the same to a table.
However I then wanted to just pick one with exact matching ID's I select on a form.
I could not see an easy way to apply this without making another set of nested querys which seems a little excessive
Anyway, an easy way for the printed report to do this is a simple filter added after, works great.
I can't see a way to do the same for an update query.
I was wondering if I could get the record source of this report and add to my table. I have tried with
' Dim db As DAO.Database ' Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(Me.RecordSource, dbOpenDynaset) ' Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
And dozens of variations over some hours but a variety of errors mainly "too few parameters."
I am printing statements for clients. Depending on certain criteria about the client, the format of the statement changes completely. This is just how the boss wants it, I have no control. I still want to generate all statements at one time (not separate reports).
I currently have this working using multiple subreports, and changing their Visibility property, only one being visible at any one time. This works, but it is very slow, I assume because I am basically making Access do triple the work (I have 3 subreports).
I pretty confident this is not the intended use of subreports. Is there a "correct" way to do what I am doing? I know I could just manipulate everything with VBA, but having subreports makes editing the different formats very easy.
I need for Access only to sum the "hours worked" of my payroll data, only if another field, "pay code" meets a certain criteria.
In Payroll, some codes are used to designate actual hours worked like REG (Regular Hours) and like OTS (Overtime Straight) while other codes are added as "premiums" to these hours and do not mean that you actually worked those hours, like OTP and HOD (Overtime Premium & Holiday Premium). The hours are only added as a reference to the actual hours worked they are attached to. Their units should not be counted as hours worked.
I want Access to 1) Group By Cost Center, 2) Sum "Hours Paid" that are tied to a REG or OTS (only), but 3)Sum the Earnings for all pay types.
Basically, (in excel-like terms: Hours Paid Column= SUMIF of Paycode = REG OR OTS. But Earnings Amount Column= Sum of all Paycodes)
Cost Center Job Type Hours Paid Pay Code Earnings Amount
I am tyring to set up a query, that runs from a selected date range, to return only the records that don't meet the requirments. For example, I have four fields:
Name Date Attended Required
I want to start the query from a date range usesing Between [StartDate] And [EndDate]. There are mulitpule records containing the same date. Then from there I want to return only the Names that don't attend the required number of sessions.
Ex. Attended = 2; Required = 4; Return name Attended = 3; Required = 3; Don't return name
Would this be possible with one query? If so, how would I go about setting up the criteria. If not then how would i link the 2 queries together?
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code: Private Sub Form_Current() 'Code 1 - Prevent edits if 21 day period has lapsed If TestDate2(Me.txtStartedHidden) > 0 Then MsgBox "Editing NOT allowed - 21 day time period has been reached" Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days Edits prevented if invoiced date reached & records past 21 days
How do I run a report based on a table, but with criteria? For example, I have a few check boxes on my table and I want to run a report of all the people who have the boxes checked. Thank you!
This is in regards to my same database file posted in my last thread. Basicly it's a work order database. Each task has an employee and the quality of work they did. 1=Bad, 5=Great...so it's from 1-5. I need to create a report of all the tasks which had a quality score of 3 or below for the previous month. I'm good with SQL, but I can't figure out how these Access reports work.
I want my client to be able to double click on the report and have it generate. It would be nice to have it save to a PDF or Word doc, but that would be a plus. Right now I just need to know how to generate reports based on a criteria. I searched the web for 2 hours and could not find anything about this.
I have following table, forms and reports in my access database:
Tables: 1- maintbl Query: 1- mainquery based on maintbl Forms: 1- Input or mainform based on mainquery 2- unbound form: to Filter report Report: 1-mainreport based on mainquery
Objective:
I want to filter report on followings: Name(Text) Month(Text) Year (Number)
I have 3 combo boxes on ubound form and want to filter report based on three above mentioned criterias.
