IIF(AND) statement

Hi
I have a number of things I need to test in the query. I need to use IIF 
statement with AND (for example in Excel you can do it),.
Example
I need this
IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2")
how to do it in access?

-- 

Greatly appreciated

Eva
0
Utf
4/7/2010 7:09:01 PM
access 16762 articles. 3 followers. Follow

3 Replies
1351 Views

Similar Articles

[PageSpeed] 37

On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote:
> Hi
> I have a number of things I need to test in the query. I need to use IIF
> statement with AND (for example in Excel you can do it),.
> Example
> I need this
> IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2")
> how to do it in access?
>
> --
>
> Greatly appreciated
>
> Eva


IIf (cat="HS" and cat2="JTK" and cat3="NT","1","2")

Groeten,

Peter
http://access.xps350.com
0
XPS350
4/7/2010 7:17:13 PM
Try this --
IIf (cat="HS" or cat2="JTK" or cat3="NT","1","2")

-- 
Build a little, test a little.


"Eva" wrote:

> Hi
> I have a number of things I need to test in the query. I need to use IIF 
> statement with AND (for example in Excel you can do it),.
> Example
> I need this
> IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2")
> how to do it in access?
> 
> -- 
> 
> Greatly appreciated
> 
> Eva
0
Utf
4/7/2010 7:51:02 PM
Hi
I used finaly or, but your post helped me to think it over. Thank you!
-- 


Greatly appreciated

Eva


"XPS350" wrote:

> On 7 apr, 21:09, Eva <E...@discussions.microsoft.com> wrote:
> > Hi
> > I have a number of things I need to test in the query. I need to use IIF
> > statement with AND (for example in Excel you can do it),.
> > Example
> > I need this
> > IIf (and(cat="HS",cat2="JTK",cat3="NT"),"1","2")
> > how to do it in access?
> >
> > --
> >
> > Greatly appreciated
> >
> > Eva
> 
> 
> IIf (cat="HS" and cat2="JTK" and cat3="NT","1","2")
> 
> Groeten,
> 
> Peter
> http://access.xps350.com
> .
> 
0
Utf
4/7/2010 9:31:02 PM
Reply:

Similar Artilces:

Electronic Document Delivery
Is there any way to adapt EDD to send statements? It seems off to have to maintain email addresses in two separate places in a relational dataabase. ...

Update statement with Incrementing
I would like to use an update statement to update a group of records and have one of the fields update incrementally beginning with 1 (just for this group). Below is an example of code that I thought would work and the error that is being generated. Any ideas? UPDATE dbo.vw_NHSReadyToBill SET BatchID = 470, ClaimID = (SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS GetCount FROM vw_NHSReadyToBill) Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subq...

IIF function help in query
Hello, I have the following field in the query to look at a field PartDesc, if any of the text have a *BUSH* word, return yes, otherwise no. BUSH: IIF(PartDesc= Like *BUSH*, "yes","no") Why the Like *BUSH* not working? Thanks Because = and LIKE are two operators, a little bit like: 4 * / 3 would mean what (multiply followed by divide)? Probably better to remove the =, in this case: BUSH: IIF(PartDesc Like *BUSH*, "yes","no") Vanderghast, Access MVP "Cam" <Cam@discussions.microsoft.com> wrote in ...

Help with If Sum statement
Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 1...

Macro for Date If Statement
I have a column of Dates, need the Macro to look at all the date in the column and compare to the current date and if the date in the column is more that 365 days old want to turn the date in the column GREEN! Can this be done? Currently there are over 500 dates in the column. Thanks for your help Ray Column? Are the dates in a table? If so then the following will work (with column1) of the table containing the cursor Dim oCell As Cell Dim oRng As Range With Selection.Tables(1) For Each oCell In .Columns(1).Cells Set oRng = oCell.Range oRng.End = oRng...

money essentials and online statements
I purchased Money Essentials about a month ago. I was able to connect to and download info for National City Bank that one time. I haven't been able to download info from the bank to Money since then. Also, I have never been able to connect online with ING Direct. Any ideas would be greatly appreciated! ~Mary ...

VBA Loop If statement
Good morning, Please could someone help me with this. I currently have the following piece of code in my workbook: Sub ProdCheck() Dim Answer As String If Range("B2").Value = "ESX" Then Range("AB2").Value = 10 '*****this is where the ELSEIF argument of the code should be***** Else: Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Does this product exist?") If Answer = vbNo Then MsgBox "You pressed NO!" Range("AB2").Value = 0 Else MsgBox "You pressed Yes!" R...

A SQL statement in VBA
On the main form is a subform in datasheet view and 5 command buttons. On the click of one button I have an event procedure that should run this query : SELECT DISTINCT [Client Extended].ID_client, [Client Extended].date_ouverture, [Client Extended].nom_client, [Client Extended].date_naissance, [Client Extended].couriel FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client = Produit.REF_client WHERE ((([La première date :])<=[date_livraison]) AND ((Produit.date_livraison)<=[ La seconde date])) GROUP BY [Client Extended].ID_client, [Client Extend...

VBA Coding for DoCMD Statements.
I am running the following code and it works well EXCEPT the "close form" line doesn't want to work. I am not getting an error message, it just won't close the form. Is there something misworded here? Function Another_Record() MSGBOX "Do you wish to do another LRU update?", 4, "LRU UPDATE" If vbYes Then DoCmd.GoToControl "Serial Number" Else DoCmd.Close acDefault, , acSaveNo End If End Function Try Docmd.Close acForm,,acSaveNo And to be really safe you might want to specif...

Conditions with a nested IIF
I am trying to analyse in a report performance based on two date fields (fldTargetDate and fldActualDate). The control source for the particular control on the report is: =IIf(IsNull([fldActualDate]),"n/a",IIf(DateDiff("d",[fldTargetDate], [fldActualDate])>7,DateDiff("d",[fldTargetDate], [fldActualDate]),"OK")) Since the fldActualDate may at times not yet be filled in, I need to test for a null value. but the code produces an "OK" result for every row (regardless of whether the target is met or not or whether there fldActualD...

Case statement comparing two ranges
Dear All I have writtent some code but I belive I have done it the long way and think there is a much simpler way of doing things. I am trying to compare two ranges in a case statement. Below is the code, can anyone help with reducing the code required or a more efficient way of doing it Tahnsk Regards Private Sub Worksheet_Change(ByVal Target As Range) Dim rngData As Range, rngCell As Range Dim Wday1data As Range, Wday1Cell As Range Dim icolor As Integer Dim WrkDays As Date Dim StartDate As Date Dim Enddate As Date Dim Day1 As Integer Dim icolor2 As Integer Dim i...

What are the {'s either side of my if statement?
when I edit the cell they disappear and my formula display #value! eg: {=IF(SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0))>0, SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0)),"")} This means that you have an array formula, that is it is using what are typically single cell functions against an array of cells. The { } are just a way of showing in the formula bar that it is an array formula. An array formula is committed using Ctrl-Shift-Enter, not...

Help with SQL Statement for pivot table
I'm trying to put a sql statment from an access query into a pivot table query builder. I built the query in access then took the sql view and tried to put it into the excel query builder -- not working. Access put dbo_ in front of all of the table names so I took that out but it's got other problems too. Not sure how the date range should be specified and I wanted it to search a text field for the word "implant". In access I did this by specifying *implant* but sql doesn't seem to like it. Here's the statement: SELECT ApptPending.CreateDate, ApptPending....

Can download web statements from one user login but not another in XP
When I login into money as user1 I can download my credit card statement into my without a problem. But when I am logged in as user2 the downloads will not work. The computer quickly does something when I initiate the download but I don't get and error message and nothing happen. Note the same file is used on both login cases? Any help out there?? ...

a statement on multiple backups
I've always stated that having multiple backups is a good idea, well last week I found out how good of an idea it was, I had recently installed a new dual core cpu ie faster better etc. I made a backup before I did the upgrade, I also installed a new larger hd for primary drive C, well to my sad suprise I found my latest update to my backups corrupted everything, and I had only one copy of my backups ie Image of Drive C. anyway I ran into multiple issues, bad SATA cable, etc, it's taken me all week to get it up and operating, when Id rather be outside playing in the sunsh...

Crash when printing one specific client statement
Has anyone had a problem with one specific client becoming corrupted so you cannot print their statement? -- Christopher Fazio VMD Chris Fazio | 2009-03-01 | 5:29:32 PM wrote: >Has anyone had a problem with one specific client becoming corrupted >so you cannot print their statement? I have not seen that problem. I assume you're using one of the Business versions of Money. If you give us specifics of your issue, maybe we can help figure it out. Tell us things like: * Type of hardware * Operating system version * Version of Money (Help > About) * Exactly what happens ...

Bug: Cannot import brokerage statement
I just installed Money 2005 and then added my 401k account (Prudential Investments). When it asked me what transactions I wanted to download, I selected all. After the download finished I go to the Review investment activity page. I see a list of all the investment activity. I click the Next button to update Money to this statement. I get a dialog stating that a security was found where it already exists but under a different name. There are two radio buttons, the first to confirm that it is the same security, the second to add it as a new investment. I select the first(default) and the...

emailing statements #2
Does GP 9.0 only work with Adobe for this feature? If so, which feature? Can one license be used across the network, or do I have to buy a license for each user that may want to email a customer statement? It does. MS seems to have married itself to Adobe for some reason. You will need an Adobe license for each person who will use it. -- Charles Allen, MVP "Dwayne" wrote: > Does GP 9.0 only work with Adobe for this feature? If so, which feature? > Can one license be used across the network, or do I have to buy a license for > each user that may want to email ...

Credit Card Statement Download M05...
I am having an issue with my GM Credit card. I have it set up to download the statement every 2 days. I also have some recurring bills set up to automatically enter them into my credit card account (such as the estimated payment for this card so my balance forecast for my checking account is accurate). When Money downloads the statement, it doesn't try to match the transactions. My checking account downloads the statement every day, and it does match the transactions. It just adds another transaction for the same amount. Is there a setting for each account as to how to handle ...

iif statement 10-10-07
Im back again, if anyone can help I have 2 fields that when the result is seleceted as below Level 1 pass (First field) and level 1 pass (2nd field) I want the result "Full award" or Null I have tried the following statement below Result: IIf(([aon portfolio result]="Level 1 pass" And [aon exam result]="Level 1 pass"),"Full Award",Null) It will run the query but only after acting as a parameter query, but will not show the result full award ehwn the criteria is met Help Me again I have kind of solved problem? The 2 fields are both look ups,...

Need Help with an IF statement
IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]=[CSAandSELinventory]![LOWLIMIT],"YELLOW") IIF([CSAandSELinventory]![CountOfSERIAL_NUMBER]>[CSAandSELinventory]![LOWLIMIT],"GREEN") IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]<[CSAandSELinventory]![LOWLIMIT],"RED") I am trying to build a pie chart report on a form. I am using the above to assign colors based on my inventory levels. I know this will not fill the pie chart with color, but it gives me the information I want. Any help would be appreciated. I worked it down to this: COLOR: IIf(...

SQL Statement #2
Anyone know the SQL statement(s) to delete items from the database that haven't been sold since a certain date? Are you sure you don't want to just make inactive if it hasn't been sold by a certain date? This would be the much preferred method. Otherwise you will lose history data in your reports. "Krista" <Krista@discussions.microsoft.com> wrote in message news:868A839B-96B1-4F11-BC49-ECF256BE7172@microsoft.com... > Anyone know the SQL statement(s) to delete items from the database that > haven't been sold since a certain date? On Jun 5, 7:19 a...

If Statement #3
I have a formula in a spreadsheet at work which requires more than seven nested if statements to give me a true answer, is there a way for excel to do this or is there some other function which could be used instead of if. This is a problem i have had for a while so any suggestions would be appreciated. Patrick ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Patrick, There are many options, for instance this recent reply and responses shows a couple IF(G13=0,IF(I1...

Nested IF Statements
How many nested IF statements can I have? I have 20 possible numeric values which can be in cell B1. These numeric values represent a part description. I want to be able to automatically populate cell B2 with the description of the numeric value in cell B1. This works when I limit the number of IF statements to 7, however, I need 20. Is there any other way to do this? Hi Tony, Yes 7 is the limit for nesting functions. Generally one uses a lookup (VLOOKUP or HLOOKUP) in cases like yours. Read Help and return with questions if needed. Chip has some suggestions at www.cpearson.com ...

If-statement (deal with date and time)
Hi all, What formula should I input into the cell if I want to check th current date and time against a given date and time, i.e. If current date and time equals a month (say, June) then display a formula (say, = A1 + B2) else 0 I have wrote such code: = IF(MONTH(NOW()) = -a month- , -formula- , 0) but it does not work. I am using xl2000. Thanks -- Message posted from http://www.ExcelForum.com The 'month()' function returns a number from 1-12, so make sure you if() function checks for a number, not the name of the month. Alternatively, you can define all your months as number...