Queries :: Getting Parenthesis Error In Nested IIF Query
Apr 15, 2015
I have different product groups and they can have one of 3 rental rates(5,4,3%) I have written a nested query but I keep getting parenthesis errors, the last one I got told me I must enclose iif functions in parentheses. I know its probably a small thing but I just cant see it. By the way the server is German so ; need to be used instead of ,.
IIf([view_India]![Sub_Group] In (1730;1810;2870);5;iif Left([view_India]![Sub_Group];2) In (01;02;21;24;29)
;5;iif([view_India]![Sub_Group] In (3310;3330;3340;3360);3;4))))
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#];
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.
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on: 1) A Type field from my table 2) A calculated date based on other query fields.
My query has the following fields: [Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then if [Action 2] Is NotNull, Null if [Action 1] is NotNull, [Action 1] + 10 workdays if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
Some essential background first. I have a Balances table which records balances by date. I also have an Issues table where problems are logged. There is a one-to-many relationship between Balances and Issues (i.e. each Balance can have multiple Issues). I also have a Comments table where updates for each Issue are recorded. There is again a one-to-many relationship between Issues and Comments (i.e. each Issue can have multiple Comments)
There are two key date fields in the Issues table :FlagDate (the date an Issue was flagged by a user for investigation)
ResolveDate (the date said investigation was brought to a conclusion)
There is also a date field in the Comments table :UpdatedWhen (the date any given comment was added)
So the basic flow is that an Issue gets flagged (FlagDate), then various comments are added (multiple UpdatedWhen's) and finally the Issue gets resolved (ResolveDate)
I need to incorporate a trend graph which will show the counts ofNew (i.e. new issues flagged as of each day) Cleared (i.e. issues resolved each day)
Updated (i.e. issues not yet resolved but updated each day)
Unchanged (i.e. issues not yet resolved and not updated each day)
Outstanding (i.e. all unresolved issues as of each day)
This is the SQL I've put together to get that table of information on which to base my chart :
Code: SELECT [tblBalances].[BalanceDate] AS AsOfDate, (SELECT COUNT([tblIssues].[IssueID]) FROM [tblIssues] WHERE [tblIssues].[Flag] = True AND [tblIssues].[FlagDate] = [tblBalances].[BalanceDate]) AS New,
[Code] .....
The subqueries for 'New', 'Cleared' and 'Outstanding' work perfectly; the resultant dataset gives me one record for each date in the Balance table and correctly counts the number of issues falling into each of those buckets.
The problem I have is with the 'Updated' bucket. If a flagged issue happens to be updated twice on the same day (which is perfectly acceptable), it counts this twice as well. I don't want this as I just want to know how many issues were updated on any given day - not how many updates there were.
I tried using COUNT(DISTINCT) in the 'Updated' subquery but it gives me a syntax error - on further research, I don't think it's possible to use the DISTINCT keyword in a COUNT subquery (at least not easily)
I also tried grouping by IssueID within that 'Updated' subquery but it still gives me the duplicate count within the same IssueID (and returns nulls rather than zeroes for those days where no updates occured)
I think I need to add a subquery within the subquery () to only return the latest comment as of the date in question - something along the lines of :
Code: (SELECT TOP 1 [tblComments].[UpdatedWhen] FROM [tblComments] WHERE [tblComments].[IssueID] = [tblIssues].[IssueID] AND DateValue([tblComments].[UpdatedWhen]) <= [tblBalances].[BalanceDate] ORDER BY [tblComments].[UpdatedWhen] DESC) AS UpdatedWhen
But how to do this, nor if it is even feasible in Access to begin with.
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity from( SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center FROM [Open Items] )a left outer JOIN [Open Items] AS [Open Items_1] ON (a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity) Where ([Open Items_1].MonthEndDate=#8/31/2014#)
I'm new to working with Access Iif statements. I'm attempting to change data values in an existing table for column named "Status." Some of the data in this column has the correct value (Active) that needed represented, while the rest is populated with either a "J" which is Active or "T" which is inactive. I needed an update query that will ignore any values that are already "Active," and convert any values that are "J" to Active and values that are "T" to "Non Active." Any help would be greatly appreciated. Below is the function I have written that's full of syntax errors:
IIf(([STATUS] T,"T") = "T", then "Non Active"), IIf([STATUS] J,"J") = "J" then "Active"), else Active = "Active"))
I have a table which gets data from an external text file. I imported this into an Access table (version 2003).
I have a field (PersonName) in the table (Employee_Credits) which has data in the following format.
FirstName LastName (EmployeeID)
It is always in the same format. Using an SQL Query in MS Access, how can I extract the contents within the parenthesis and insert into a column titled “EmployeeID”
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 am trying to create a database which crosschecks specifications against building codes. My problem is that it's not feasable to put all of the information about a Building into the Room table, though many codes require information about the Building in order to classify the room. How would I count (or list/get details from) a room in the Rooms table, for use in an SQL statement about the Buildings table.If there are up to 5 rooms in the building, do at least 2 rooms contain a fire escape?
My Rooms table contains information about if the room has a fire escape, as well as references its parent Building ID. How would I query the rooms to count the Rooms within all buildings, and subsequently query all Buildings which have less than 2 rooms with fire escapes?I need it to return a list of the buildings which do not conform to this code.
Now this is literally my second day of programming/working with Access, but I've yet to find an answer dispite hours of searching.I think I can adapt it for the 100+ building codes/references I'll need to implement. It would be perfect to find an SQL-only way of doing this, without going into modules etc...
I have a set of two queries (which I'll call A and B, for convenience). Query A reads a table containing customer order numbers, a sequentially-assigned line number for each item requested by a customer on a particular order, and a part number associated with each order line. The line numbers are not integers, but run out to three decimals - so 1.000, 1.100, 1.150, 1.200, etc. would all be valid line numbers. All Query A does is to sort the table, with the primary (first) sort being order number, and with line number being the secondary (2nd) sort. Each record also contains a field equal to the integer value of the line number, so for lines 1.000, 1.100, 1.150, 1.200, this third value would always be 1; similarly, for lines 2.000, 2.100, and 2.200, the third value would be 2.
Query B uses query A as its source of input, and performs grouping (using the "Group By" function) so that query B's output displays only one record for each unique combination of customer order number and INTEGER line number; the part number displayed on each row of query B's output is supposed to be the part number on the first row of query A's output whose order number and integer line number correspond to each group in query B. (I am using the syntax of "First(Part Number)" in query B to report this.) This seems to be working most of the time, but not consistently; sometimes, for each unique order number / integer line number combination, Query B reports a part number from the wrong row of Query A. So, for example, order 12345, integer line 1, as reported from query B, may report the part number on line 1.300 from Query A, instead of line 1.000 from query A as intended.
Does anyone know a fix for this? I've got a team of anxious coworkers who are hoping for it.
I am trying to calculate the time between two dates where one date field might be blank or not. Where the field is blank I want to use the current date to perform the calculation. So far I have the following but I keep receiving an error message saying that the expression has a function with too many arguments. Is there a simpler solution to this?
IIF([LastOfEnd_Date] IS NULL,(DateDiff(w,[LastOfDischarge_Date],NOW()))/4, IIF(Not isnull([LastOfEnd_Date], Abs(DateDiff("w",[LastOfDischarge_Date].[LastOfEnd_Date]))/4
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 three tables, tblPeople, tblAddresses and tblVehicles. I have a form called frmPeople that shows a single person record, and has subforms with the list of addresses and vehicles for that person (each record has several). I want to create a single search form with each of the fields in my tblPeople, tblAddresses and tblVehicle tables, and allow the user to type anything into any of the fields on the search form, and have the search form fetch records that match.
So far, I have the query built, and the tables all linked the right way (I think), and I have all the search fields coded. And it works GREAT... except for this one little issue: If I do a search, and I put "Smith" in the last name field, I will get several Smiths in my search result, one for each address and vehicle combination. In other words, if I have ONE Smith in the table, and two vehicles and two addresses for that Smith, the query results give me FOUR Smiths! One with Vehicle1 and Address1, one with Vehicle2 and Address1, one with Vehicle1 and Address2, and one with Vehicle2 and Address2.
What I would LIKE is to have ONE record in the query results, and that record shows me that one Smith. And if I then search for "Plymouth" in the VehicleType field, and I have Smith in the name field, I'll get ONE record, and it will be the one of Smith with his Plymouth (Vehicle2).
Here is how my query looks right now (this one ONLY has the last name and vehicle search function to save space), and this is the one that gives me multiple results (which I do NOT want, unless multiple PEOPLE match the search criteria):
Code: SELECT DISTINCTROW tblPeople.LastName, tblPeople.FirstName, tblPeople.PersonID, tblAddresses.Address, tblVehicles.Vehicle, tblVehicles.Plate, tblVehicles.VehicleYear FROM tblPeople LEFT JOIN tblAddresses ON tblPeople.PersonID = tblAddresses.PersonID WHERE (((tblPeople.LastName) Like "*" & [NameField] & "*") AND ((tblVehicles.Vehicle) Like "*" & [VehicleField]));
I have four fields that typically have a quantity amount in them.
I also have a field with a date.
I would like to say something like this
If fielda = 0 and fieldb = 0 and field = 0 and fieldd = 0 and in the date field, the date is >30day from today’s date add a 1 to this field, otherwise leave it blank
I have been doing some experimentation with Queries and I have discovered something unusual.
If I have one query and I use the totals feature (only using group by) I get all the records based on the inputed criteria
If I then use that same query as an underlying recordset for a second query and I use the totals feature (only using group by) in the second query... I only get the first record of each type. See query results below..
I am trying to achieve the results of the second query in the first query (DON'T WANT NESTED Queries) and I don't really understand why the results of the queries are different when everything else is the same other than one being nested.
Example below
First Query Results (using totals with group by)
1 a 1 a 1 a 2 a 2 a 2 a 3 a 3 a 3 a 4 a 4 a 5 a
Second Query Results with the First query as its recordset (using Group By)
SELECT * FROM ((TBL_Properties INNER JOIN TBL_Property_Landlords ON TBL_Properties.Property_ID=TBL_Property_Landlords. Property_ID) INNER JOIN TBL_Landlords ON TBL_Property_Landlords.Landlord_ID=TBL_Landlords.L andlord_ID) INNER JOIN TBL_Agents ON TBL_Properties.Agent_ID=TBL_Agents.Agent_ID;
so far. But I need to join the results from another query which will be calculating SUM(Pay). Is it possible to INNER JOIN to a: (SELECT ....) nested query?
hi guys, i have this sql that filters 2 tables to give me the highest number against a record and then i want to update the 1st table. but it dosnt seam to work it brings back the correct data but the sql has generated a non-updatable query.
any ideas?
UPDATE [SELECT ARCtblErrorInfo.Uber, Max(ARCtblHistory.Status) AS MaxOfStatus FROM ARCtblErrorInfo LEFT JOIN ARCtblHistory ON ARCtblErrorInfo.Uber = ARCtblHistory.Uber WHERE (((ARCtblErrorInfo.Status)=121)) GROUP BY ARCtblErrorInfo.Uber]. AS test INNER JOIN ARCtblErrorInfo ON test.Uber = ARCtblErrorInfo.Uber SET ARCtblErrorInfo.Status = [test].[MaxOfStatus];
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
The situation is I have two queries, one uses the other as a source, and the other uses DSUM with the source query as it's domain.
For example: I have one query called RetailCDState, this prompts the user for the name of a state (i.e. Texas), and then prompts for the name of the CD (i.e. Joshua Tree). The query returns the number of CD's sold for each retail store entered for that state.
I have another query called, TotalRetailState. This query uses RetailCDState as it's source. I pull some fields down into the QBE so that the CDname, state, artist name, etc will be shown. This works.
HERE IS THE PROBLEM. I have created 3 expression fields: TotalCDSOLD, TotalCDSHIP, and TotalCDRETURN. In these expression fields I have used DSUM, I want to use the query RetailCDState as the domain, which the help files said I can. However, it's giving me an expression error saying that it can't find the 'state' or 'CD Name', which is what should prompt the user when the whole thing is executed.
I get 3 error messages, one for each expression field, and then it prompts the user for the correct input, but then doesn't display the information. Here is an example of my DSUM line TotalCDSHIP: DSUM("[NumofCDSold]","RetailCDState")
I shouldn't need any criteria, because the results of the RetailCDState is what I want to total.
