I'm trying to use IIf in a query criterion, but not having any luck.
I have a field called 'ayr_code' which has values in the format:2015/6
2014/5
2013/4
2012/3
2011/2
2010/1
2009/0
2008/9
I want to return all records where in this field:if the current month is November or December, the first four characters of 'ayr_code' are between five years ago and the current year;
If the current month is between January and October, the first four characters of 'ayr_code' are between six years ago and last year;
So, in October 2015, I'd like to see records with an 'ayr_code' of 2014/5, 2013/4, 2012/3, 2011/2, 2010/1 or 2009/0. In November 2015, I'd like to see records with an 'ayr_code' of 2015/6, 2014/5, 2013/4, 2012/3, 2011/2 or 2010/1.
I've tried to do this using solution one below, but this is not working. I've gone for solution two, but I'd like to know why solution one doesn't work and if there is a way to amend it so that it does.
Solution one [preferable; not working]
Create the following field:
Code : Expr1: Left([cam_sas.ayr_code],4)
Add the following criterion:
Code : IIf(Month(Now())>10,>=Year(Now())-5 And <=Year(Now()),>=Year(Now())-6 And <=Year(Now())-1)
So this should get the first four characters of 'ayr_code' then apply one of two criteria on the results based on whether the current month is after October or not.
This takes the first four characters of 'ayr_code' then if the current month is not after October it adds one to the result, after which it applies the criterion that the final output must be between this year and five years ago.
Ok, I made a query that returns a Case Manager, their associated cases, and 3 dates that relate to each of these cases. So its:
Case Manager-----Case Name----Date1-----Date2-----Date3
I only want records to appear that have a value in at least 1 of the 3 date fields. If they have no data in ANY of the date fields I want those records excluded. How do I build this?
I have a Form that is used to print a report and the report is based off of a query.I dont like the WHERE statement in the query. I want the user to enter the employer codes and not have to continue to press enter when they are done entering 5 employer codes.
Example: The want to send letters to 3 Employer codes but after they enter the 3rd employer code the query will continue asking for 21 more employer codes. How can I provide the user a way to not be asked for 21 more employer codes? The current code for the query is:
Code:
SELECT Format([Enter Letter Date],"mmmm dd"", ""yyyy") AS [Letter Date], dbo_partfile.prt_employer_code, dbo_employer.mis_description, UCase([kn_key_name]) AS UID, Trim([prt_last]) AS [LAST], Trim([prt_first]) AS [First], dbo_partfile.prt_middle, dbo_partfile.prt_addr1, dbo_partfile.prt_addr2, Trim([prt_city]) AS City, dbo_partfile.prt_state, dbo_partfile.prt_zip_code, Format([prt_zip_ext],"0000") AS Zip4, Format([Enter Term Date],"mmmm dd"", ""yyyy") AS [Term Date], IIf([prt_sex]="F","Ms.","Mr.") AS Title, dbo_partfile.prt_status, dbo_partfile.prt_local_nbr
I have a query based on a table that has a column called Calculated Card Expiration:
IIf([Card Type Issued]="PIV",[Issue Date]+1095,IIf([Card Type Issued]="Non-PIV",[Issue Date]+1095,IIf([Card Type Issued]="Flash",[Issue Date]+365,Null)))
It worked fine when I ran the query the first time, but now when a Card Type Issued and Issue Date is added or changed it doesn't work.
I provide a daily report to my warehouse team that pulls information from a data table for the previous day's activities. Since we don't work on the weekends, my statement needs to be manually modified on Mondays to pull the information from Friday and not from Sunday:
WHERE REF_Cal.Date = Date()-1 becomes WHERE REF_Cal.Date = Date()-3
Is there a way that I can write the above statement so that it automatically looks at Friday's numbers [i.e. Date()-3] if today is Monday? In Excel I could write a formula to accomplish this as follows: IF(TEXT(TODAY(),"DDD")="MON",TODAY()-3,TODAY()-1)
Where would you put the INSERT INTO statement in a query? Would it go after the select statement but before the From statement or would it go at the end?
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
I would like to have the user select one or more values in a list box, and use those values as criteria in a query. Then the query will be used as a source for list boxes in another form based on the values of the previous selection.
Example:
User selects options 1, 3, and 4 from the list box on Form A. The query then sets the criteria to anything related to 1 OR 3 OR 4. Then in another form, Form B, all the data tied to option 1 is in its own list box, all the data tied to option 3 is in its own list box, and all data tied to option 4 is in its own list box.
I need to execute this query from an ASP . Net page. I'm connected to an Access 2002 DB.
SELECT tblBudget.GroupCode, tblBudget.GCDescriptioon, tblBudget.Amount FROM tblBudget GROUP BY tblBudget.GroupCode, tblBudget.GCDescription, tblBudget.Amount ORDER BY tblBudget.GroupCode;
If I delete tblBudget.GCDescription and tblBudget.Amount from the "GROUP BY" instruction it does not work.
What I want to do is that in case tblBudget.GroupCode is repeated (e.g. several records with the same group code) I want to display only one line summarizing all info for the given group code.
I can create a select query with a combo box in it by changing the property of one of the fields - making the display control to combo box and adding the values. No problem there.
What I want to do is to be able to create a query dynamically from vba using a CreateQueryDef statement.
The problem is that you can only pass an SQL string to CreateQueryDef, and the combo box values are not part of the SQL code, but are saved with the query (presumably the same way column layout gets saved).
Is there a way to create a query with VBA and include non-SQL layout criteria so I can have combo boxes?
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 Union Query (that works perfectly fine) with the following code:
Code: SELECT * FROM sbqryUseBattery UNION SELECT * FROM sbqryUseBeltsDeck; UNION SELECT * FROM sbqryUseBeltsHydro; UNION SELECT * FROM sbqryUseBeltsPTO; UNION SELECT * FROM sbqryUseFiltersAir UNION SELECT * FROM sbqryUseFiltersFuel UNION SELECT * FROM sbqryUseFiltersOil;
I am using this information on a Report.
The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report?
I'm trying to build a query with an iif statement to calculate expiration dates within a query. For some reason what i'm trying to do is not working as expected.
Here's the situation. Every employees with an exception of two employees in the company has an expiration 2 years after the class took place. The two exceptions are the certified instructors who's training certification is good for three years.
I've created a query with the employeeid, training subject, and maxoftraining date.
I'm trying to write an if statement to give me the expiration date based on the above information. Here's what I have so far; however its not calculating based on the criteria.
Expiration: IIf([tblemployee]![EmpID]=1 Or 2,DateAdd("yyyy",3,[maxoftrainingdate]),DateAdd("yyyy",2,[maxoftrainingdate]))
At this point in time, all "expiration" dates are showing + 3 years rather than just the Employee ID's 1 and 2 and the rest +2 years.
I have a form with a check box. A query is run that looks at that check box and decides what the criteria are based on that. So, if the check box is checked, it should pull in all data in the field that is a Y. If it is not checked, i want it to pull all data (Y's and N's and blanks).
I am setting up a query to report on the turnaround time for appication to be processed.
I have a calulated column to calulate the numbe rof days betwen receipt and approval of applications. I now want to be able to run the query and subsequently the report by having a prompt to enter anlower and upper number of days range.
I have used the experssion Between [Lower range] and [ Upper range] to prompt and when I enter say 1 to 5 days the query returns all days.
Any thoughts as to why it is not working and what can I do differnetly?
I have two tables each with an ID field (autonumber/PK/No Dup etc).
I want to append two fields from one table to the other table. I have set up an Append Query to do this but it won't work - I get the following error - "The INSERT INTO statement contains the following unknown field: 'FiID'...."
I'm trying to run a very basic iif statement to correct hourly data for sorting. Basically, a trading day runs from 8am - 8am, so I need to adjust the hours to ensure that 1am on the 15th trading day (really the 16th on the calendar), comes after 9am on the 15th trading day (which will actually be the 15th on the calendar).
Here's what I've used. It's driving me bananas, because it keeps telling me that there's a syntax error (comma) in the query expression, but I can't understand why?
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code: TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund SELECT [Client Accounting].[Marketer] FROM [Client Accounting] GROUP BY [Client Accounting].[Marketer] PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
I would like to have a table update query populate cells in a field, based on an IIf statement (below), but states that the IIf statement arguments still need to be enclosed in parentheses. I don't understand what the problem is, I have two sets of opening / closing parentheses.
I am having a problem to get my query to run properly. I have a huge IIf statement that doesn't seem to be working but I am not sure what is wrong with it. What it is supposed to do is return a good, actionable, or poor based on a specified weight and an actual weight. So what the query does is pull information from a table based on the specified weight and then using that information it should give back the correct rating in the last column.
SELECT Costs.Costs_Per_Capita, Costs.Period IIF (Period = 1, (Select AVG(Costs_Per_Capita) From Costs Where Period = 1), (Select AVG(Costs_Per_Capita) From Costs Where Period = 2) AS result FROM Costs;
But get "syntax error (missing operator) in a query expression ..."
I am using a simple " >= [BeginDate:] and <= [EndDate:]" in the criterion section of a query to allow a date range to be selected in the query which is used in a report. How can i get that [BeginDate:] and [EndDate:] info available to print on the report so it shows the actual date range being displayed in the report ? thanks.
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.