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);
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 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).
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",
I have coded a select statement that uses multiple tables for information used to populate a report. It returns the correct information, sort of, but it doesn't recognize the already existing table relationships so I get dupicate records. Is there a way to run the code so that it recognizes the table relationships?
Columns of data contain date fields & "Status" that indicates where a case lies. I'm trying to calculate days spent in a specific area (determined by status code) and total amount of time spent there all together. Status code begining w/ "I***" means case was sent to an area, w/ "R***" it returned. I have it together up to a point. My problem lies with end of expression where I want to calculate last date at specific location to now. In line 1 it would be from 5/03/07 to now since no r162 follows & blank field follows. Problem is with second IIF - it acts independently instead of giving false value from first IIF... i think.
stat2n4: IIf([status2]="i162" Or [status2]='i062' Or [status2]='i009' Or [status2]='i159' And [status3]<>'r162' Or [status3]<>'r162' And [Status4]="r162",DateDiff("d",[date2],[date4]),IIf(IsNull([status4]),DateDiff("d",[date4],Now())))
I have placed a tab control within a tab control (which I have done before). The problem is that the sub tab control appears on every page of the main tab form (encountered this before). I fixed it in the past by placing code on the on open event to make the sub tab control not visible unless the appropriate tab is selected. That worked fine.
I have tried this in the new program and find that when I set the value of the main tab form to 4 (the page index of the tab) the sub tab form will not display at all. However if I set it to zero it will display regardless of which tab is selected. Confusion has now set in :confused: . Any thoughts out there on what I have done incorrectly.
[/CODE]Private Sub Form_Open(Cancel As Integer)
'Me.OrderBy = "RankNo desc" 'Me.OrderByOn = True If TabCtl44.Value = 4 Then TabCtl144.Visible = True Else TabCtl144.Visible = False End If End Sub[CODE]
Poor old Access. I think I've made it wave a white flag, but based on the following expression I am trying to get it to evaluate, I'm not too surprised...
The situation is that I have a large number of tick boxes on a form, which nicely filter through to a series of fields in a table whose values are either -1 or 0 (according to if the box is ticked or not). Unfortunately, I now need to generate a small piece of text for a report based on which boxes have been ticked. Deep breath...
The logic seemed to work nicely when there were just 3 options in the list, but when I try to enter the above, Access says "the expression you entered is too complex". Poor baby! :D
Does any one have any idea how I can get it to evaluate the data I need?
I am facing a problem trying to calculate the commission for the order I have within my Access application.
I pull up the information from three tables to determine the commission. Commission is based on Currency then subtotal then percentage for each specific client.
Can you please look at the below query and tell me what seems to be the problem.
If the commission is anything other than USD, CAD, EUR or GBP I will calculate normal commission.
Commission: IIf(Currency="USD" Or "CAD",IIf Quantity>1000,USD_Commission2*Quantity,USD_Commission1),IIf (Currency="GBP" Or "EUR",IIf(Subtotal>100000,Subtotal*EUR_Commission2,EUR_Commission1),I If(Subtotal>10000,Price*Quantity*Commission1)))
I'm trying to add a set of Nested IF statements, but it's not showing me the proper result. I have a table which shows the classes that each student has taken based on the term. So I change the letter grade they receive to a numeric value, once I get that, I need to add those numeric values. Here is the nested IIF statements.
SUM(IIF( ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A" , 4.0 , IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A-", 3.7, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B+", 3.3, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B", 3.0, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B-", 2.7, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C+", 2.3, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C", 2.0, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C-", 1.7, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D+", 1.3, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D", 1.0, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D-", 0.7, IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "F", 0.0,)
)))))))))))) as ppp
from temporary2
GROUP BY spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, sfrstcr_term_code, sfrstcr_crn, ssbsect_subj_code, ssbsect_crse_numb, sfrstcr_grde_code, GPA
When I run this, it doesn't add the values, It just changes the value from the letter grade to the number and that's it. I need them to add. I'm I missing something?
I think I have looked at this too long!!! If "tblJobs.ServiceTypeID" is other then 1 or 7 it still returns 3%..... wheres my error!?!?!?!? :confused: Commission: IIf([JobInvoicePaid]=True And tblJobs.ServiceTypeID=1 Or 7,(([ContractPrice])-Nz([JobSubAmount]))*0.03,([ContractPrice])-Nz([JobSubAmount])*0.05)
I'm having a problem writing a query with nested iif's. This query is suppose to manipulate a text field that with #'s. Each field is either 5 or 6 characters containing a date such as "20607" or "10607" which is in the mm,dd,yy format. Can you have a nested iif statement such as below with multiple with 3 possible true conditions and only one false condition?
SELECT [date last stmt] Iif(Instr(1,[date last stmt],"0")=1,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2), IIf(Len([date last stmt])=6,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2), IIf(Len([date last stmt])=5,Left([date last stmt],1)+"/"+Mid([date last stmt],2,2)+"/"+Right([date last stmt],2,"error")))) AS Newdate FROM Exercise1;
Each time I attempt to run this query I receive a syntax error in query expression"[date last stmt]"
I would like to alter this statement so that any number less than 20 is "PRIMARY", any number between 20 and 26 is "SEC" and any number greater than 26 is "SEC2". I think I need to add another IIf() but I'm not really sure of the syntax.
Can anyone give me any guidance on how to use IFF statements in microsoft access? or Has anyone got a link to a good website that can help me? I need to use the formula below to determine if the quantity of an order from a store is greater than the quantity that is in stock at the warehouse and produce a message message.IIF([quantityordered])(=[quantityinstock]"enoughstock","notenoughstock") I just dont know where to start, i didnt even know you could enter formulas in Microsoft Access so i really need help
Hi, I have another problem with my Access database now...
I am trying to disable fields when a certain value is chosen. I can do it for one field with this code:
If ALLOCATION_LP = "Other" Then OTHER_ALLOCATION_LP.Enabled = True Else OTHER_ALLOCATION_LP.Enabled = False End If
That one works perfectly well, but I have another one that needs to disable two fields instead. If they choose "No Extant PP", I want it to disable two fields; one combo box - PLNG_PERMISSION_STATUS_2 (which I can get to and it works) and also an "Other" text box - OTHER_PLNG_PERMISSION_STATUS_2 (this is where i get stuck). I have tried two If statements, but whether I'm writing them wrong, I'm not sure.
'construct a where clause as required If Nz(Me.Manufacturer.Text, "") <> "" Then Where1 = "WHERE MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' " End If
Me.list.RowSource = _ "SELECT ID, Description, Par, MaxCoins, PayLines " & _ "FROM MachineTypeQuery " & _ Where1, Where2 & _ <- how do i get something like this to work "ORDER BY Description;"
Exit Sub Where2 comes from another control and has to be filled in before they get to this control. also "where1" and "where2" are set as public stings
I want to check eight Yes/No fields. If none of the boxes are checked I want to display a msgbox. I can link seven of the fields (no problem), when I add the eighth it doesn't work.
Am I limited to seven fields in my statement? Is there a way around this?
Any help would be appreciated.
============
If Me.Printer = 0 And Me.Hardware = 0 And Me.Software = 0 And Me.MSOffice = 0 And Me.Network = 0 And Me.NewComputer = 0 And Me.Profile = 0 And Me.Other = 0 Then
MsgBox "You must enter the type of problem(s) addressed with this customer", vbQuestion, "Type of Problem(s)"
My database has a field named "Note". This field will either have a "Y" or "N" in it. At the bottom of a report I'm writing, I want to count the number of "Y's" (not the "N's") at the bottom of my report. Can someone tell me how to do this? Thanks so much in advance!
