Queries :: Multiple Nested IIF Statements - Output In One Field
Mar 25, 2013
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",
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.
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);
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.
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).
The following SQL statements run with no errors, but I can't see the output of the SQL statements. What is wrong with my "Debug.Print" or "rs" statement ? I would like to see the results. The SQL statements works when I save them to a query.
Dim db As DAO.Database Dim rs As DAO.Recordset Rem Dim intResult As Integer Dim strSQL As String Set db = CurrentDb
I have a query that I just can't figure out. We are a facility that works on rail cars. We have over 200 cars in house at the moment and the database has a daily production report in it with the status of all of the cars.
I need a query that will pull out the "AAAA" and "XXXX" cars from the list only IF the cleaning field or the mechanical inspection or BOTH fields are blank.
I am trying view the query output's in different text on the form and I do not want to see them again in query window.How do I hide this.. comments ???
i have got 2 tables i am trying query against. in table 1 i have a list of records which only appear once in the table under 1 field. in table 2 list of records which may have the same record appear under the same field numerous of times with different data in other fields on the table. i have created the reationship between both tables but i am getting multiple records appear in the output of the query where i only want the record to appear once in the output.
I'm trying to make a nested if then statement in a query field, and I can't figure out why I can't get my formula to work:
Volume: IIf([MethodCode]="K",[total]*12.54*0.026873,IIf([MethodCode]="S",([length]*[width]*[depth])/2,IIf([MethodCode]="M" And [Location]="SH",[total]*5.08*0.026873,IIf([MethodCode]="M" And [Location]="C",[total]*18.58*0.026873," "))))
I keep getting the "data type mismatch in criteria expression" error. If I separate out all the individual if then statements individually, they work. But if I connect them all as a nested if then it doesn't work.
I have a query which works fine for one if statement, but i neewd to have 10, whats the best way of sorting this, can it be done in one query, or should i use a module or should i use the code?
I would like to update a lot of fields in my tables; about 120 different fields in 12 different tables. I would however like to keep it simple by using the SQL editor in MS Access by writing all the UPDATE queries and leave them to run while I do smething else. It seems to me however that you can only run one query at a time using the SQL editor. I thought I could separate one SQL editor from the other by using semicolon as in Oracle SQL editor etc., but that does not seem to work. Does anybody know how to do this in Access without writing a VBA code etc.?
I am writing a query that is master-detail in principal. But need other data from other 2 tables. I wonder if I can construct the query so that it contains the result set of more than one SQL statement. Thank you for your kindly help.
I'm trying to put multiple IF statements into one procedure like the below:
Dim Answer As Integer Answer = MsgBox("Have You Selected The Correct Outcome?", vbCritical + vbYesNo, "Continue Request?") If Answer = vbYes Then 'Works fine here 'The bit I can't get right
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","")))
I have a table where I have 5 columns, (BatteryL1, BatteryL2, BatteryR1, BatteryR2 and BatterySize).I want to update BatterySize, with a value if any one of the above listed columns contains a specific value using something like 'LIKE' in the query.
For example: UPDATE Customers SET BatterySize=13 WHERE BatteryL1 LIKE '%13%'
I am working on my first access project (please be patient and try to explain like to a child). My problems however are past the basic level so please bear with me ....
I am working on a datbase which deals with laboratory data from patients which have different date stamps - ie. each record has a 'valid time' (time of blood drowing) a 'transaction time' (time of entry into the DB) and 'delete time' (deleted recordes are just given a date and not really deleted).
The idea is that the user should be able to watch the status of the DB at different past dates (ie what did the doctor know about a patient blood tests a month ago when he made the decision to give him a medication which eventually harmed his kideneys). The user should also be able to change values of records, delete records (which means to add a date to the deleted date field) and to add new records.
I want to do it all using one form.
Things I have done so far: 1. Query by form which locates a specific record (Main-Form & singleRecQuery) 2. A subform in Main-Form which displays the results of singleRecQuery (Single-subform). The query and subform aer updated by a macro once I enter all the data into main-form. 3. A qury by form derived from Main-Form which locates a range of values (Range-subfrom & Range-Query)
My problem spans both forms and queries (so I am posting in both forums): 1. I want to create an update query that will draw its data from Single-subform (the form which displays the results of singleRecQuery). I have created the qury without a problem but the fields in the form are locked for entering data (maybe because they are bound). Is there any way to enter data in such a form which is based on a query and then use the new data in another query ? (I tried allowing data entery in the forms properties but it deos't work).
2. I have tried to put a criterion in the queries on the "Delete Date" field so that the quries will not display the deleted records (I have tried "is null" "is empty" and "is missing") but when I run the query with these parameters I get no records back.
3. In the form I have a command buton which runs a macro that runs the qury and then update dthe subform. Is there a way to get this button to run different macros after different events in the form or do I have to add a button for every specific task I want.
4. As I have mentioned the Main-fom is supposed to run 3 differnt queries and display the results on the same form. I know how to make a subform invisable but is there a way to place all three result subforms in the same spot ?
I have a query that is called up when data is selected from a form and you hit the submit button to go get the report. The report calls up the query which all is working except the if statement in the query criteria. Here is a look at the code.
Like IIf(IsNull([Forms]![TPKG_Status_Generator]![System_List]![column(1)]),"*",[Forms]![TPKG_Status_Generator]![System_list]![column(1)])
What it should do is if the field selection in the form is left blank and they hit submit then the query should return everything. If they pick a system number then it should only display the items in the query that have to do with that system. My problem is that no matter if I leave it blank or pick and item in the drop down box it still displays every line item. Any thoughts?
I have done smoe baisc queries to sort, filter and report on data.How do I create a nested query where I don't repeat all the information across the query results line? Is there a way I can build a nested query to give all the SMCode codes associated with each appointment in one column separated by a coma?I have a table with appointments (APPTS):
RecID ADate ATime Durantion PVID Desc Case#
Then I have a table with Orders related to these appointments (ORDERS):
RecID Case# SMCode SMDate
Here's my query:
SELECT DISTINCTROW Appts.RecID, Appts.ADate, Appts.ATime, Appts.Durantion, Appts.PVID, Appts.Desc, Orders.SMCode FROM Appts INNER JOIN Orders ON Appts.[Case#] = Orders.[Case#];
