Complex Nested If Statement

Jan 10, 2007

Hello,

I have been working on a query for a few hours now to create a new field based on data in other fields in my table. Here is the code I am using:

MBkt: IIf([Table14].[OWNERGRP_1]<>"",IIf([Table14].[OWNERGRP_1]="ADVO",IIf([Table14].[PRICINGMKT]="SHAREDEX","SEZ"),IIf([Table14].[PRICINGMKT]="REP","REP"),IIf([Table14].[PRICINGMKT]="RIP","REP"),IIf([Table14].[COV_FREQ]="Weekly PCD","PCD"),IIf([Table14].[COV_FREQ]="Weekly","ADVO WKLY","ADVO MULTI-WEEKS"),IIf([Table14].[OWNERGRP_1]="ANNE",IIf([Table14].[OWNER_1]="MMSI/MAIL MARKETING/ANNE","ANNE MMSI","ANNE"),"SOLO"))

Unfortunately, I am unable to get this to work. Any help that anyone can provide is greatly appreciated.

Thanks,

-Brian

View Replies


ADVERTISEMENT

Nested Iif Statement! Not Sure Why This Query Will Not Run...help!

Aug 12, 2007

I'm wanting to run an update query but first I want to see the results by using a select query to make sure that I'm getting the correct results.

See the following query statement:

SELECT ([Goodrec - All] INNER JOIN WH_ACCTCOMMON ON [Goodrec - All].ACCTNO = WH_ACCTCOMMON.ACCTNBR) INNER JOIN [Trial-GL-Reference] ON WH_ACCTCOMMON.CURRMIACCTTYPCD = [Trial-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode], [Goodrec - All].CURRBAL = IIf(Not IsNull([chgoffamt]) And Not IsNull([partsold]),CCur([grossbal])-CCur([chgoffamt])-CCur([partsold]),IIf(Not IsNull([CHGOFFAMT]),CCur([grossbal])-CCur([CHGOFFAMT]),IIf(Not IsNull([partsold]),CCur([grossbal])-CCur([partsold]),[grossbal]))), [Goodrec - All].PCTOWNED = IIf(Not IsNull([origamt]) And Not IsNull([partsold]), CCur([origamt])-CCur([partsold]/CCur([origamt]), IIf(IsNull([partsold]), [grossbal]));


This is the error that I receive each time I attempt to run it and I'm not sure what I'm missing.

Error message:

MICROSOFT OFFICE ACCESS
Syntax error(missing operator) in query expression '([Goorec-ALL] Inner Join WH_ACCTCOMMON ON [Goodrec-All].ACCTNO=WH_ACCTCOMMON.ACCTNBR) inner join [TRIAL-GL-Reference].LOANTYPE SET [Goodrec - All].GLCODE = [TrialTotals].[glcode]'


Any help with this issue would be greatly appreciated.

Thanks.

Govmate!

View 3 Replies View Related

Nested SQL Statement In A Function?

Apr 18, 2008

I have a table of dates that store all the Sundays of a year, as that is what all of our records are based on (how many items an employee sold on the week ending on such and such a date). The table is called WeekEndDates and the column is called WeekEndDate. I want to automate adding a new week, so I have a command button that has the following VB code:

DoCmd.RunSQL ("INSERT INTO WeekEndDates (WeekEndDate) VALUES (DATEADD('D',7, SELECT Max(WeekEndDate) FROM WeekEndDates)")

Any help would be appreciated, I don't know of another way to do this. I'm not sure if this is a SQL question or a VB question, so sorry if this is in the wrong spot.

View 1 Replies View Related

Nested If Statement Syntax Error

Aug 29, 2007

Hello,

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

View 11 Replies View Related

Queries :: Nested IIF Statement In Date Calculation

Jul 30, 2015

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

View 5 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

Forms :: Nested IF Statement To Check If Input Box Is Null On Form

May 9, 2014

I have a form in my Access database that has 3 input boxes by which a user can locate a record by. The problem I am running into is that I can not get a nested IF statement to work properly to first check which of the search boxes are filled in and second search by whichever is filled in. I have come up with the following code that seems to be only searching by the first input box. If the first input box is blank it does not move to the second or third numbers so I imagine there may be something wrong with the order of my IF statement or the syntax is off.

View 14 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

Nested If (I Think)

Sep 9, 2005

Hi

I have just designed a central training database to aid in the running of our training department.

One section is for an IT course which contains 8 modules.

The tests have the following IDs

Mod 1: ID05, ID06, ID07, ID08
Mod 2: 2.1, 2.2, 2.3, 2.4
Mod 3: 3.1, 3.2, 3.3, 3.4
Mod 4: 4.1, 4.2, 4.3, 4.4
Mod 5: 5.1, 5.2, 5.3, 5.4
Mod 6: 6.1, 6.2, 6.3, 6.4
Mod 7: 7.1, 7.2, 7.3, 7.4
Mod 8: 55, 56, 57, 58

One person may take any of the tests.

If someone completes Mod1, 2 and 7 they achieve Level 1

Similarly if someone completes Mod 3, 4, 5, 6 and 8 they achieve Level 2.

I need to run queries to find who has completed L1 or L2 or both.

Obviously they only achieve if they pass so in that table there is a status field with

Pass
Fail
Wait

As the options

Queries have never been my strong point and I was hoping someone here could help me.

I’m using XP Pro, with Office 2003 and the office 2000 db format.

Many thanks

Dazzy

View 1 Replies View Related

Nested IIF

Oct 26, 2006

Hi

how would one go about nested IIF

New Sal : IIf([emplsalaryaa]>80000,([emplsalaryaa]+([emplsalaryaa]-80000)/(100)*9)+7713.98)

i have the above but i need to put one more

iif

i have tried , IIF but it does not work

i don't know why?

Asad

View 3 Replies View Related

Nested IIF

Jun 8, 2007

My first post ever... so here goes.

My Goal:

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.

status1 date1 status2 date2 status3 date3 status4 date4
1) I162 5/3/07 I004 5/20/07 I006 6/1/07
2) I162 4/2/07 R162 4/30/07

My failure:

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

View 9 Replies View Related

Nested Tab Controls

Oct 3, 2005

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]

View 5 Replies View Related

Nested IIf Nightmare

Oct 20, 2006

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

institution:
IIf([people].[memctf]=-1,"CTF",(
IIf([people].[memwt]=-1,"Westcott",(
Iif([people].[memwy]=-1,"Wesley",(
Iif([people].[memry]=-1,"Ridley",(
Iif([people].[memwm]=-1,"Westminster",(
Iif([people].[memiocs]=-1,"IOCS",(
Iif([people].[memermc]=-1,"ERMC",(
Iif([people].[memmbi]=-1,"Margaret Beaufort Institute",(
Iif([people].[memcjcr]=-1,"CJCR",(
Iif([people].[memcym]=-1,"CYM",(
Iif([people].[memindepba]=-1,"Independent BA",(
Iif([people].[memhmc]=-1,"Henry Martyn Centre",(
Iif([people].[memregchelm]=-1,"Regional (Chelmsford)",(
Iif([people].[memregnor]=-1,"Regional (Norwich)",(
Iif([people].[memregpboro]=-1,"Regional (Chelmsford)",(
Iif([people].[memregalbans]=-1,"Regional (St Albans)",(
Iif([people].[memdiocedsips]=-1, "Diocesan (Eds and Ips)",(
Iif([people].[memdiocnorwich]=-1, "Diocesan (Norwich)",(
Iif([people].[memreg]=-1, "Regional", "Unknown"
)))))))))))))))))))))))))))))))))))))

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?

View 4 Replies View Related

Nested IIF Question

Mar 30, 2007

I have a nested IIF that I cannot get figured out. This is what I have so far:

Hours: IIf([Dept]="10",55,Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))

The problem is that I need another IIF to differentiate between Regular and Overtime. It needs to do this.

If Dept = 10 Then
55 Regular Hours and 0 Ovettime hours
Else
Format(IIf([EorDCode]=" 1",[Regular]/60,[OT]/60),"Fixed"))

View 1 Replies View Related

Nested IIf Problem

Aug 13, 2007

Hi,

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

View 3 Replies View Related

Adding Nested IF

Oct 4, 2007

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.

SELECT 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,

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?

View 6 Replies View Related

Problem In Nested IIF

Nov 28, 2007

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)

Thanks

View 12 Replies View Related

Nested Iif Problem

Dec 14, 2007

Hello,

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

Any help is appreciated!!!!

View 3 Replies View Related

Nested IF For Two Comparisons

Sep 19, 2012

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.

SEC_NR: IIf((Left([BAY_NR],2)>=26),"SEC2","PRIMARY")

View 3 Replies View Related

Nested Junction Tables

Jan 5, 2008

I'm at a place in my design where I don't want to go further without advice for fear of really making a mess.:o

I have Products, Customers, Customer Locations, and Customer Models that can have any number of combinations. I've joined what I think makes sense at the lowest level, customers with locations, and next model with customers/locations.

I think products is the outer most junction, because many customers can use the same product in any number of models and locations.

The attached diagram shows where I am now--multiple junction tables, and junctions of junction tables. Now each of these tables has other relationships to nomalize things, but those are not shown.

Is one more level, or nest if you will, the right way to do this?

View 8 Replies View Related

Nested Grouping And Sums

Jun 10, 2005

I did a search for Gouping and sums, but nothing really helped me.

What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.

Vendor, items, Cost, Date1

- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this)
- Now I am trying to Filter this by date1 (Easy, I did this)

But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:




Vendor A Brush 100 1/1/2005
Vendor C Book 50 1/2/2005
Vendor A Brush 100 1/3/2005
Vendor B Pencil 10 1/4/2005
Vendor A Hat 50 1/5/2005
Vendor B Pencil 10 1/6/2005
Vendor B Pen 10 1/7/2005
Vendor B Eraser 10 1/8/2005
Vendor A Shoe 40 1/9/2005
Vendor D House 1000 1/10/2005

Doing a search from 1/1/2005 - 1/9/2005 we get:

FORM OUTPUT:

Vendor A Brush 200 2
Hat 50 1
Shoe 40 1
Vendor B Pencil 20 2
Pen 10 1
Eraser 10 1
Vendor C Book 50 1

View 3 Replies View Related

Nested Query Help Needed

Apr 26, 2006

Hello everyone,

I’m looking for some help with a “nested query”

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


Thanks a lot

Corey

View 2 Replies View Related

Qry Nested If's And Date Formats

Jul 11, 2007

I'm tring to build a queary based on a field called "PLAN_CODE" and a policy issue date field. There are 3 plan codes ...select1, select 2 and select 3.
I want to pull information as follows
Select1 has an issue date 11 months ago (day the queary is ran)
select2 has an issue date 23 months ago
select3 has an issue date 35 months ago

I built a field to calculate the duration of the policy based on the issue date
Duration: (Round((POLICY_ISSUE_DATE]-Date())/365,1)). I think the format is wrong? need help with that!

I built a field to create a nested "IF" statement but the first "IF" is not pulling correctly, so I cant built the remaining "IF" statements.
Expr2: IIf([PLAN_CODE]="SELECT01N" [Duration]=-0.9,0).

Anyone have a better approach? Any input how to handle this? I'm not real good with VB code but have a general understanding of it. It's easier for me to build in the QBE.

View 2 Replies View Related

Nested Queries And Forms

Jul 23, 2007

Hey,

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 ?

Sorry it is so long
Thanks
EJ

View 1 Replies View Related

Nested Query Confusion

Sep 23, 2007

Dear Access Expert

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)

1 a
2 a
3 a
4 a
5 a

View 3 Replies View Related

Nested Select Statements

Oct 4, 2007

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!!!!

the new and refund fields are checkboxes.

View 3 Replies View Related







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