Queries :: Using IIf Statement In Query Criterion

Jun 5, 2015

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.

Solution two [non-preferable; working]

Create the following field:

Code : Expr1: IIf(Month(Now())>10,Left([cam_sas.ayr_code],4),Left([cam_sas.ayr_code],4)+1)

Add the following criterion:

Code : >=Year(Now())-5 And <=Year(Now())

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.

View Replies


ADVERTISEMENT

Query Criterion Question

Sep 15, 2005

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?

View 3 Replies View Related

Forms :: Using A Variable From A Form To A Query As A Criterion?

Feb 23, 2015

I want to use a variable from a form to a query as a criterion. but how?

View 1 Replies View Related

Queries :: WHERE Statement In Query

Sep 11, 2014

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

[code]...

View 4 Replies View Related

Queries :: If Statement Not Updating In Query

Jun 23, 2015

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.

View 5 Replies View Related

Queries :: Flexible WHERE SQL Statement In Query

Apr 10, 2013

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)

View 2 Replies View Related

Queries :: Put INSERT INTO Statement In A Query?

Apr 3, 2013

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?

View 2 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

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?

View 2 Replies View Related

Queries :: Using Or Statement From Multivalue Listbox In Query

Jul 3, 2013

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.

View 12 Replies View Related

Queries :: Limit On Embedded IF Statement In Query

May 23, 2014

I have the following code

Code:
Gap: IIf([Q1]=2,"GAP",IIf([Q2]=2,"GAP",IIf([Q3]=2,"GAP",IIf([Q4]=2,"GAP",IIf([Q5]=2,"GAP",IIf([Q6]=2,"GAP",
IIf([Q7]=2,"GAP",IIf([Q8]=2,"GAP",IIf([Q9]=2,"GAP",IIf([Q10]=2,"GAP",IIf([Q11]=2,"GAP",IIf([Q12]=2,"GAP",
IIf([Q13]=2,"GAP",IIf([Q14]=2,"GAP",""))))))))))))))

I need to add Q15 and Q16 but there appears to be a limit on how many IIf statements I can embed and I get an error that my formula is too complex.

View 3 Replies View Related

GROUP BY Just One Criterion

Jun 11, 2005

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.

Thank you

View 1 Replies View Related

Queries :: Create A Query Dynamically From Vba Using A CreateQueryDef Statement?

Nov 19, 2013

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 using Access 2002.

View 6 Replies View Related

Queries :: Make A Nested If Then Statement In A Query Field

Apr 27, 2014

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.

View 6 Replies View Related

Queries :: How To Sort A Union Query SQL Statement For A Report

Oct 1, 2013

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?

View 14 Replies View Related

Queries :: IIF Statement To Calculate Expiration Dates Within Query

Jun 15, 2015

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.

View 2 Replies View Related

Queries :: IIF Statement In Query Criteria Based On Check Box

Jun 19, 2014

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).

here is my criteria:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","*")

This does not seem to work. I have also tried:

IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y",Like "*")
IIf([Forms]![frm_Query_Form]![CheckBox]<>0,"Y","like "*"")

View 14 Replies View Related

Parameter Criterion Not Working

Feb 7, 2006

Hi to all

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?

Regards

mgerada :(

View 6 Replies View Related

Queries :: Append Query - INSERT INTO Statement Contain Unknown Field

Sep 19, 2013

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'...."

View 2 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Queries :: IIF Statement - Syntax Error (comma) In Query Expression

Aug 4, 2015

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?

Sort2: IIf([DELIVERY_HOUR]<8,[DELIVERY_HOUR]+24,[DELIVERY_HOUR])

View 14 Replies View Related

Queries :: SQL Crosstab Query With Multiple Grouping Levels - Transform Statement?

Mar 24, 2015

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");

View 2 Replies View Related

Queries :: Update Table Query Populate Cells Based On IIF Statement?

May 6, 2013

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.

Code:
IIf IsNull (=Mid( [CONTRACTOR_TASK]![TASK_NAME] ,6,6))

View 3 Replies View Related

Queries :: IIF Statement Query - Pull Information From A Table Based On Specified Weight

Aug 4, 2015

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.

Here are the conditions:

Poor:
AW(Actionable Weight)<Lower Actionable Weight
AW> Upper Actionable Weight

Actionable:
Lower Actionable<AW<Lower Acceptable
Upper Acceptable<AW< Upper Actionable

Good:
Lower Acceptable<AW<Upper Acceptable

The lower acceptable, actionable, and upper acceptable, actionable are different numbers based on the specified weight entered.

Here is the IIf Statement I currently have and I wasnt sure if order mattered or what I was doing wrong..

Rating: IIf([LOWER ACTOINABLE]<[Actual Weight]<[LOWER ACCEPTABLE],"Actionable",(IIf([LOWER ACCEPTABLE]<[Actual Weight]<[UPPER ACCEPTABLE],"Good",(IIf([UPPER ACCEPTABLE]<[Actual Weight]<[UPPER ACTIONABLE],"Actionable","Poor")))))

View 6 Replies View Related

Queries :: Average IF Statement-syntax Error (missing Operator) In Query Expression

Jun 5, 2014

I am trying to get Average If function to access sql. I have columns Period and Costs_Per_Capita, result should be like like this:

Costs_Per_Capita Period CALCULATED_Period_Avg_Costs
15,505 1 15976.27582
16,368 1 15976.27582
16,037 1 15976.27582
15,995 1 15976.27582
15,000 2 16000
17,000 2 16000

I tried:

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 ..."

View 8 Replies View Related

Using Criterion To Add Begin/end Date Range. How Get On Report

Mar 26, 2007

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.

View 2 Replies View Related

Queries :: IIF Statement With And In True Statement

Oct 31, 2014

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.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved