help with nested iif statement

I have a table and I want to create a query that has a new field "
Type of Shipment" base on a field "Item Category"

If the item category field equals ZDIR, ZTRN ...etc up to 23 different item 
categories the "Type of Shipment field will equal DIRECT else I would like it 
to return "Stock"
Any help will be greatly appreciated.

Thanks
0
Utf
12/30/2009 1:59:01 AM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1108 Views

Similar Articles

[PageSpeed] 34

Hi,

for the iif statement. What you can do is:
IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','')

Having a long nested if is a headache. What i can suggest is to add the field
to the table. 

Help with complex nested iif statement wrote:
>I have a table and I want to create a query that has a new field "
>Type of Shipment" base on a field "Item Category"
>
>If the item category field equals ZDIR, ZTRN ...etc up to 23 different item 
>categories the "Type of Shipment field will equal DIRECT else I would like it 
>to return "Stock"
>Any help will be greatly appreciated.
>
>Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

0
Benjamins
12/30/2009 2:47:59 AM
Hi,

for the iif statement. What you can do is:
IIf(category = 'ZDIR' OR category = 'ZTRN' OR .............,'DIRECT','STOCK')

Having a long nested if is a headache. What i can suggest is to add the field
to the table. 

Help with complex nested iif statement wrote:
>I have a table and I want to create a query that has a new field "
>Type of Shipment" base on a field "Item Category"
>
>If the item category field equals ZDIR, ZTRN ...etc up to 23 different item 
>categories the "Type of Shipment field will equal DIRECT else I would like it 
>to return "Stock"
>Any help will be greatly appreciated.
>
>Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1

0
Benjamins
12/30/2009 2:48:50 AM
On Tue, 29 Dec 2009 17:59:01 -0800, Help with complex nested iif statement
<Help with complex nested iif statement@discussions.microsoft.com> wrote:

>I have a table and I want to create a query that has a new field "
>Type of Shipment" base on a field "Item Category"
>
>If the item category field equals ZDIR, ZTRN ...etc up to 23 different item 
>categories the "Type of Shipment field will equal DIRECT else I would like it 
>to return "Stock"
>Any help will be greatly appreciated.
>
>Thanks

If the relationship between the category and the output is ever likely to
change, then I'd really suggest adding another field to the Item Categories
table containing "DIRECT" or "STOCK", and just use that field. Someday you
might want to add other values ("DISCARD", "INDIRECT", ...???), and you'll
surely want to be able to add or change categories without having to dig
through a nasty complicated IIF statement.

That said... try

TypeOfShipment: IIF([Category] IN ("ZDIR", "ZTRN", "..."), "DIRECT", "STOCK")

-- 

             John W. Vinson [MVP]
0
John
12/30/2009 4:40:59 AM
Reply:

Similar Artilces:

How open an help dialogBox when we push a button
Hi, I'm starting in MF I want open a new dialogbox with some information about the software when I push a button I made the .rc file (IDD_MAIN for the main frame and IDD_INFO for the Info frame What I should made for open the IDD_INFO dialogbox when I'm pushing the button Info?? Thank Lolo Hi, You need to first create a new dialog box using resource editor. Then open the "Class Wizard"( Ctl + W ), which will then prompt you to create a new class for the dialog that you have created( say CMyDlg ). CMyWnd::OnButtonPress() { CMyDlg dlg; dlg.DoModal(); } Cheers Ja...

TABLE Function/Array HELP!!!
{=TABLE(,B13)} Can anyone tell me what this array function is doing and how it works? Any help is greatly appreciated! It is not an array function. The cell is part of a Data Table. You create Data Tables (in Excel 2003 and before) in the Data>Table menu command. In Excel 2007 it is in the Data tab, Data Tools block, What-if analysis drop down. Data tables should not be confused with Tables in Excel 2007. -- Kind regards, Niek Otten Microsoft MVP - Excel "chris" <csnishimoto@yahoo.com> wrote in message news:0ba2e359-df45-4c9d-9216-2aa091f83ef7@u12g2000prd.googlegroup...

New problem encountered
I posted the below message earlier which I have been having problems with the last two weeks or so and will include the new problem I just encountered. I have Win 2k Server and Exchange 2k Server. I'm using Outlook to send/receive my e-mail. I have two e-mail accounts setup. First, POP/SMTP which I am using with my ISP (pop.shaw.ca). Second, Microsoft Exchange Server which I use to for my mydomain.com. There is a second domain.com which someone else is using. What I can do: E-mail mydomain.com to seconddomain.com E-mail my shaw account to seconddomain.com What I can't do: Receive o...

please help dist list
all: upper management just asked for a dist list with 6 members as to: recipients and 3 members as cc: recipients. running exch2k3 sp1 know how to create dist list and add members but i dont see any way to distinguish who is to and who is cc is there any method for that? any ideas? Thanks Ed no...you'd need to create two different distribution groups... "EdMull51" <EdMull51@discussions.microsoft.com> wrote in message news:4EC90F83-840B-43B9-99D1-9816056659BE@microsoft.com... > all: > > upper management just asked for a dist list with 6 membe...

Excel formula not calculating help!
i have enclosed my file. i wrote the formula's : =sum(c15:e15) and they are not working unless i double click and then it works. shouldn't i be able to type a number in on c15 and automatically it should calculate it in the "sum cell" as well? please help!! Attachment filename: cale_new.xls Download attachment: http://www.excelforum.com/attachment.php?postid=416812 --- Message posted from http://www.ExcelForum.com/ Hi caomychao! Try: Tools > Options > Calculate Select Automatic -- Regards Norman Harker MVP (Excel) Sydney, Austra...

Help With Numbers To Dates
Hello, I am trying to paste into Excel, columns of numbers (from Word) that are separated by hyphens. Excel is converting most of these numbers to a date format. How can I stop this from happening? I have attempted every Special Paste feature that Excel 2005 has. THANKS Billy Hampton Select the columns in XL that you intend to use to receive these numbers from Word, and *PRE-Format* them to text. Then, simply copy and paste. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefi...

having problems with a multiple conditions nesting formula
DATA (in colum A cells 1-5): 14 11 12 16 8 ISSUE: How can I automatically populate YES or NO into column B beside each data point in column A based on the following scenario. I need to know if the data point in column A is >= 20% higher than all of the prior data points would also work. So B1 would be N/A because there is no prior data point for A1, B2 would be yes if the data point in A2 is >= 20% higher than the data point A1, B3 would be yes if the data point in A3 is 20% higher than either A1 OR A2, B4 would be yes if the data point in A4 is 20% higher than eith...

Begginer Help
At the moment I am working on one of the excel file and I found on object in file can you tell me what is it? and how can I insert it. Vira +------------------------------------------------------------------- |Filename: Book2.zip |Download: http://www.excelforum.com/attachment.php?postid=3718 +------------------------------------------------------------------- -- viralbhat ----------------------------------------------------------------------- viralbhatt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2650 V...

Access 2007 corruputs Access 2000 files if you edit a query. Help!
I have a database that I made a small edit to on my Vista machine running access 2007. The database is on a network shared drive (not ntfs). The query still opens fine on access 2007, but on my XP machine running office 2000, when I open or try to edit the query, Access 2000 just closes with no error message at all. There is also no entry made in the application event log. When I attach my debugger, Visual Studio reports an unhandled exception at 0x1b018900 in MSACCESS.EXE Access violation writing 0x00040ffc The debug window in the debugger also lists: First-chance exception at 0x3008753...

Automating earnings statements
We are interested in either: creating a location where employees can securely look up their earnings statement; or where we distribute each individual earnings statement to a private e-mail address (where the employee can choose to either print or not). Any advice for either option above would be highly appreciated. Thanks in advance, Kerry Business Portal's Employee Self Service module called Employee Pay has this functionality. I've got a couple of clients using this module. One is a hospital with about 750 employees. It's very easy to use. -- Charles Allen, MVP ...

Need Help!
The Project Nations Bank has 7 customers (make up your own names, put your name as the first) holding a saving account that pays interest each month at the annual rate of 8% (monthly rate is then 8/12%) and charges $2.00 each month for service. The balances in these accounts at the end of January are: $1250, $475, $85.30, $274.90, $342.40, $266.25 and $2469.20 Enter these data in an Excel spreadsheet and use the features of Excel to compute and display interest for January and the balance after adding the January interest and taking off the service charge (four columns). Print the spr...

DSum Countrol Source Help
I am a relatively new Access Database developer, I am becoming more familiar with the tool/calculations but I am having trouble figuring out why I get the #Error when using the DSum Calcultion. I have an unbound text box on a Form, backed by a query. The goal is to count the [# of Pages Published] for each [Action], example "New". The calculation I am entering is: =DSum("[# of Pages After]","qry_Form_DocDashbaord","[Action]=""New""") I have gone into my table to verify: 1. [# of Pages After] is a Number field. 2...

Need help @_@
So I have about 911 things to input into Microsoft Excel, and they are supposed to be seperated by 4 different categories. I tried copying and pasting everything, hoping everything would be autoformated into seperate cells, but everything just got inputted into the whole first column and only the first cell. So the first cell basically looked like: Category 1 Category 2 Category 2 | Cell 2 Info for Cat1 Info for Cat2 Info.etc| Cell 2 What I'm hoping for it to look like when I paste everything in is: Category 1 | Category 2 | Cateogory 3 Info Info | Info info | Info Info Help?:co...

Exchange HTTP Virtual Server Instance won't start. PLEASE HELP :-)
A couple of days ago we proceeded to install RPC over HTTP on our Exchange 2003 cluster. After installing Windows 2003 SP1 and Exchange 2003 SP1 we noticed that OWA was no longer working. On further investigation we found that we had to install the patch KB841561 to get OWA running again. We did this last night and everything appeared to be working fine. We could access the Exchange server via Outlook 2003 and Out Web Access via SSL. Upon returning to work this morning we noticed that the HTTP access was down again. When we looked at the IIS manager we saw that the default web site and ...

Please help! mapping new lead attribute to territory id in account
Please help - I have created a new Territory attribute in leads but I can't map this back to territory ID in accounts - I would like one lookup list of values to maintain for territory id / I would like to use the system territory id attribute in Leads as this is an attribute that I would have thought would have been available "out of the box" in Leads?? Can anyone tell me how to do this please as CRM3 won't let me make this mapping - it says the source and destination attributes are invalid for mapping. so many thanks if you can help me Hi, CRM does only support t...

Access 2007 Help Files
I recently upgraded to Access 2007. I tried using HELP to search on a specific statement and was unable to locate anything useful. Almost everything I tried stated "No Information Available". What I really need is the VBA Language Reference for 2007. Can anyone offer any suggestions? Jack Cannon I've had the same difficulty. I use 2 sources, first Access 97 help, which has an excellent, albeit for Access 2007 incomplete help file. And the second source is Access 2007 VBA Programmer's Reference by Access MVPs Teresa Hennig, Armen Stein, et. al.: http://...

How to append text from beginning to a large file with the help of any MFC class like CFile or CStdioFile.. ??
Hi, How to append text from beginning to a large file with the help of any MFC class like CFile or CStdioFile.. ?? It is able to append the text to the end but how to do that from the starting.. That should not overwrite the content of the file.. How to do that ?? ===== Regards, Jigar Meht AFAIK, there is nothing which will do this automatically for you. If its a really large file, and you dont want to read it all at once, you may want to consider writing what you want to another temporary file and then append existing file to it and then delete/rename as needed. -------- Ajay Kalra ...

P/R Earnings Statements
On the Direct Deposit Earnings Statements produced by SBM Payroll, can the Vacation and Sick Time Available information be removed or suppressed somehow ? Thanks, Tom ...

need help to complete my export to csv using macro.
Hi all, I been trying all sorts and nothing works what I am trying to do is this to convert cut n paste from a website and convert the info to an CSV file to be imported into a excel worksheet. However upon from cut n paste from website into Word (via text file to strip out images) the Word ends looking like this... Weapons Revolver 3 Attack 2 Defense Owned: 1 Pair of Brass Knuckles 2 Attack 2 Defense Owned: 37 Add to Wishlist Send Gift Police Baton 23 Attack 22 Defense Owned: 1 .22 Pistol 2 Attack 0 Defense Owned: 463 Add to Wishlist Send Gift Sub Mac...

Lists
Okay I am new to excell bar drawing little spreadsheets to do my own financing. I am currently setting up a sheet and need the users to be able to select from a list of options only and not input thier own text - does that make sense? also i ahve 12 names on the list and one master with all on - would like the data entered on to a names list to be automatically entered onto the master or vice versa which ever is easiest. sorry if this sounds jumbled but i am totally lost and this has to look really hot for work! regards Kelly - England Hi Kerry, don't know a great deal on forms ...

formula help PLEASE PT 2
I need to get the information from sheet 2 row 16 using the date in sheet 1 C4 and the same date in sheet 2 A16 sheet 1 REPORT DATE 4/12/2010 APR 11 - 18 KPI No Genesis KPI's UOM sheet2 15-Apr 16-Apr 17-Apr Apr11-17 11 19 10 18-Apr 19-Apr or can I lookup a sum of cells using a range of dates? Thanks If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what y...

Nested ifs in CSE?
Say I have data similar to the following: Col A Col B 1 3 2 6 3 3 4 4 5 1 I would like to use a CSE formula to take the sum of only the #s in Col B that correspond to #s between 2 and 4 in Col A. I tried this with no success: {=sum(if(A1:A5>=2,if(A1:A5<=4,B1:B5,0),0)) Any ideas? No array formula (CSE) needed for your situation Try on of these: =SUMPRODUCT((A2:A6>=2)*(A2:A6<=4)*B2:B6) or =SUMPRODUCT(--(A2:A6>=2),--(A2:A6<=4),B2:B6) Adjust range references to suit your situation. Does that help? ***********...

Custom Forms will not open in Outlook 2002. HELP!!!
I have 2 users that logon to a terminal server to use Office. They are trying to open a custom form which is stored in an organizational forms library in Exchange. The user gets the following error "The form requried to view this message cannot be displayed" I have been through all of the MS articles and cleared cache, deleted cache file etc etc but it is not fixing the problem. This is really frustrating! There are other users on the terminal server that can open the forms without any problems. Also if I try and create a new custom form and save it in the user's pers...

Help with excel 2007 chart
Hey, this seems fairly simple but is stumping me. I have a table with static data that shows my budgetary costs per month for the last 2 years. The row labels in my table are the categories (mortgage, insurance, power, gas, phone, etc.) and the column labels are each month for 2007 and 2008. What I want to do is create a chart that will graph the data (line chart) for each category over the last two years. I would like to choose what category to display on the chart so they're not all on the chart at the same time. I have tried a Pivot Chart, but it's just not displaying the data corre...

PP2003 every image and embedded object turned into red cross, help
I have been working of a PP presentation (in Office 2003) for a number of days. Today when I opened the document all of the images and embedded excel worksheets have turned into boxes with red crosses on them. If I double click on the red crosses that I know are the embedded worksheets then the worksheet opens in Excel with all my data displayed correctly. This shows that the correct data is in the file. However when I click outside the worksheet (i.e. back on the slide) the worksheet is shown in huge scale (i.e. just a few cells is as big as the slide). I then reduce the size o...