IIF and Like?

I need to create a indicator field that will look at a field and see if it
has a certain code in it and then return a Y or N.

I was thinking that I could use an IIF to look at the field and if it
contains a "C" or "P" or and "S" (using Like function?) then the field would
be Y and if it didnt contain any of those values it would return a N.

I use some assistance on the IIF syntax.

Very much appreciated.

Jeff

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

0
jfredel
8/6/2007 1:11:56 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
7411 Views

Similar Articles

[PageSpeed] 17

Create a query using your table.
Type an expression like this into a fresh column in the Field row:
    CPS: (([Field1] Like "*C*") OR ([Field1] Like "*P*") OR ([Field1] Like 
"*S*"))

This calculated field will contain:
- True if the letter C, P, or S is found anywhere in Field1;
- False if none of those letters are found in the field;
- Null if Field1 is null.

In the query, True will display as -1, and False as 0. Use the Format 
property of the field if you want it displayed as Yes/No. Alternatively, you 
can create a form or report and bind this calculated field to a check box.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jfredel via AccessMonster.com" <u10039@uwe> wrote in message
news:764a58ec44521@uwe...
>I need to create a indicator field that will look at a field and see if it
> has a certain code in it and then return a Y or N.
>
> I was thinking that I could use an IIF to look at the field and if it
> contains a "C" or "P" or and "S" (using Like function?) then the field 
> would
> be Y and if it didnt contain any of those values it would return a N.
>
> I use some assistance on the IIF syntax.
>
> Very much appreciated.
>
> Jeff 

0
Allen
8/6/2007 1:59:36 PM
jfredel via AccessMonster.com wrote:

>I need to create a indicator field that will look at a field and see if it
>has a certain code in it and then return a Y or N.
>
>I was thinking that I could use an IIF to look at the field and if it
>contains a "C" or "P" or and "S" (using Like function?) then the field would
>be Y and if it didnt contain any of those values it would return a N.


Does this do what you want?

	=IIf([indicator field] Like "*[CPS]*", "Y", "N")

-- 
Marsh
MVP [MS Access]
0
Marshall
8/6/2007 2:19:36 PM
Reply:

Similar Artilces:

IIF Statement syntax?
I have a timesheet template that allows a user to enter their Time In, Time out for Lunch, Time back In, Time Out for the day, then calculates the time differences to give total number of hours worked. I am trying to make it so that if the total time for the day is Negative, it assigns zero as the default. Here is the statement that I tried IIF(ROUND((((E7-E6)+(E4-E3))*24),2)<0, ROUND((((E7-E6)+(E4-E3))*24),2),0 Can anyone give me some help on what I've done wrong? I just get #NAME in the box Thanks for the help in advance Mac Could it be that IFF should be IF? Bernard "m...

Is there a MOVE operator like IF, AND, etc?
-- SgtBob And what would such an operator do? In article <CA367574-9AAA-49D8-BB28-E209532A5F5A@microsoft.com>, Bob <Bob@discussions.microsoft.com> wrote: There is no MOVE I can't think of a use for a MOVE so Explain what you'd like to do Steve On Fri, 08 Sep 2006 22:25:02 +0100, Bob <Bob@discussions.microsoft.com> = = wrote: No. Unless you have a typo and meant MODE Worksheet functions cannot move data. They can only pull from another source. Gord Dibben MS Excel MVP On Fri, 8 Sep 2006 14:25:02 -0700, Bob <Bob@discussions.microsoft.com> wrot...

How to make our window stay on top of all other applications (like modal).
Hi, I want to make one of my small preview window on top. That window should be on top of all other applications (at least should be on top of my main application.) So, if I minimize my main application that window should also get minimized. and I should be able to work in my main application. Because this on top window is just for preview purpose.. the work will be done in the main window.. If i make this window modal dialog, I am not able to work in main application as i have to close modal dialog first. So, is there any API which enables me to show non-modal dialog but it still comes on to...

Stumped with LIKE query
I have a MS Access database (from Office XP) to catalog my movies in. Here's my dilemma... I have Shrek 1 an 2. From within Access, how can I query the names so Shrek and Shrek 2 are both returned? I either get one or nothing. Here's what I have tried (among other things): SELECT Table1.MovieName, Table1.MovieYear, Table1.Category, Table1.Rating, Table1.Plot FROM Table1 WHERE Table1.MovieName Like [Enter Movie Name]; That returns only "Shrek" and not "Shrek 2". ------------------------------------------------------------------------------ ----- SELECT Table1.M...

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

iIF clause
Can you help me build the right IIf field in the query with the following conditions : The field DDU consissts of : DDU :[exworks]*2+0,4+0,01 To the above expression i must also add 0,001 if size = 205, etc according to the following table : 205 0.001 60 0.001 20 0.009 1 1.32 4 0,32 0,5 1,67 However Acces does not accept my query, obvioulsy i have errors : DDU : [exworks]*2+0,4+0,01 + IIf([size = 205],0,001,[size = 60],001) Will you help me ? ...

Just published Money-like app on Android Market
Hi, I've been working on this app for the past 6 months or so. I am a long time Microsoft Money user and wanted a migration path after the cancellation. So I created this app that runs on any Android-based phone (Droid, Nexus One, etc.) I've been running it parallel with my regular Money installation. I have about 60 open/closed accounts going back 5 years and about 20 currencies. The app is called anMoney. Just search the Android Market for anMoney on your phone to install. Currently it only supports regular accounts (no investments/loans yet), but I am planning ad...

What Does +IIF Do as Opposed to IIF?
What Does +IIF Do as Opposed to IIF?James Igoehttp://code.comparative-advantage.com/ AFAIK, there's no such function as +IIf in VBA. If I had to guess, I'd say that the + is simply arithmetic: add the results of the IIf function to whatever preceeded it.How are you seeing it used?-- Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele(no e-mails, please!)<james.igoe@gmail.com> wrote in message news:1173285991.823665.303480@p10g2000cwp.googlegroups.com...>> What Does +IIF Do as Opposed to IIF?>> James Igoe> http://code.comparative-advantage.com/> On 7 Mar 200...

Need help utilizing the "Like" function
Using Excel 2003, here is example code that doesn't work. What am I doing wrong? My isNumeric test returns "True", but there an "A" in my string. Am I not checking for all the character to be 0 - 9? Sub Test1() Debug.Print isNumeric("A00101") End Sub Function isNumeric(ByVal sInput As String) As Boolean If sInput Like "*[0-9]" Then isNumeric = True Else isNumeric = False End If End Function Thanks, - Pat Dreiding - I am not sure why you aren't using Excel's functions, but here is why your's ...

IIF question
Hello. Doing a report that uses an IIF in a textbox that is text. If the IIF is false I would like the text (mainly 1 or 2 words) not to be underlined. How do I end the IIF so the text is not underlined? Thanks. John I am not sure how you mean, but if you want this underlined based on the true or false then use the properties box and choose the text decoration item and write an expression in there. "JohnE" <JohnE@discussions.microsoft.com> wrote in message news:5C6EEFBC-0DC0-41C0-A35D-3A01209E568A@microsoft.com... > Hello. Doing a report that uses an IIF...

IIF with condition
A criteria "<100" in a numeric field is successful. Using it in an IIF statement as IIF(1=1,<100) dosen't work. What is the proper syntax? Dave Dave wrote: > A criteria "<100" in a numeric field is successful. Using it in an IIF > statement as IIF(1=1,<100) dosen't work. What is the proper syntax? > > Dave > What are you trying to do? "<100" in a criteria, for the field [MoneyBet] is saying, give me all records that have a number less than 100, in [MoneyBet]. an iff statement is: iff(criteria, , answer if criteria is...

Merge Rows of like data
I get an excel sheet from our accounting department that I import into a database for reporting. The data is straight forward in most cases, but today I noticed that there are a lot of rows that are duplicated except for two columns. Is there a macro or a way to run a script that would look at these rows and compare them and if all the columns in the row match except for these two, combine the the columns (these are number columns so I would like to add the numbers) and create a single row? If so this would really help me get the reports they need. Any help is appreciated. Here is ...

Sum(IIF statement
I have a large table that I want to query 2 things from. Please be patient because I am pretty new at this. I need to count the number of times that something appears in some fields in the table. Below is what I have tried (keep in mind there are many more fields to count)... test: [Exp1]=Sum(IIf([Account number] Is Not Null,1,0)+(IIf([Address] Is Not Null,1,0)) I would like my output to look like: KEY TEST 1 2 2 0 3 5 etc... And then I want to look in each of those same fields and if there is data concatenate it into one field on the query output and...

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

2003
I use publisher all the time. A few weeks ago, old and new files developed a problem. The images and text flash like under a strobe light. I ran the Fix installation disk and it still does it. Not sure what else to do. Not ALL the text and images flash and they don't do it continually. But most of them do and will continue to flash after stopping when I zoom in or out or click anything on the design. -- I am constantly amazed at the wealth of knowledge I do not have. Look at the Accessibility settings in the Control Panel, clear High Contrast if it is enabled. I think you ne...

OutlookExpress filters files with certain extensions (like .ASP, .PDF, etc.),
I have OutlookExpress 6. When I receive files with certain extensions (like ..ASP, .PDF, etc.), it says "OE removed access to the following unsafe attachments in your mail: filename.PDF". The file does not contain virus, but it still filters any files with this extension and other extensions like ..ASP. This did not used to happen before, just start happening recently. How can I fix this problem ? Thank you. Tools->Options->Security. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) vi...

iif and between
hello i have a set of numbers 1-52 in a field. when i query criteria ...between [weekvar] and [weekvar]-13....i get the answer i need with the query: a set of numbers. However, when i put this same condition within an iif statement in the query criteria, it doesn't work and comes up with no results. iif([weekvar]>13, between [weekvar] and [weekvar]-13, 100). The "100" answer will work, but the "between" will not query anything. What i am trying to do is to return a set of numbers while using iif(. I don't understand why it'll work when...

IIF Statement
Hello, In my Access 2003 database I have the following IIF statement in one of the modules. DoCmd.OpenQuery IIf(ans = vbYes, "qryGetMWF", "qryGetWF") This works fine but I want to change it so that a 2nd query is run in both conditions. If True I want the following 2 queries run. qryGetMWF and qryTrainingDatesMWF If False. qryGetWF and qryTrainingDatesWF How does the IIF statement need to be changed? Thanks. -- Regards, Chris "eckert1961" <eckert1961@discussions.microsoft.com> wrote in message news:F4AB3547-70C8-46AC-89DB-...

IIf
I am creating a report that I want to spell out "has expired on" and the date that I entered in the form if that date is prior to today; or to spell out "will expire on" and the date that I entered in the form if that date is greater than today. I used the following: " Furthermore, the waiver of fees that you have received " & IIf([Forms]![frm_rptDrugFeeLtr].[WvrExpDate]<='=GETDATE()',"has expired on ","will expire on ") & [Forms]![frm_rptDrugFeeLtr].[WvrExpDate] & .... When I put the = in front of GETDATE, i...

iif question
This should be an easy one for you gurus... I a cell, I have a NOW() function that keeps updating everytime a value changes. =IF(F22 = "","",NOW()) Isn't there a way to capture the date/time and "lock it" so that it doesn't change? I'm keeping track of user input date & time values. tia, -- JMorrell Take a look here: http://www.mcgimpsey.com/excel/timestamp.html In article <5FD7764D-9253-423F-A09B-3E2598D89A61@microsoft.com>, "JMorrell" <JMorrell@discussions.microsoft.com> wrote: > This should be an easy o...

I am a law student and I use Notebook Layout to take my class notes. However, the other day while I was in class my notes suddenly went from being tabbed and bulleted (like an outline) to all being le
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am a law student and I use Notebook Layout to take my class notes. However, the other day while I was in class my notes suddenly went from being tabbed and bulleted (like an outline) to all being left justified (and it no longer lets me tab). This has completely messed up my notes and I am wondering if there is a way to fix it to go back to the way it was? I already answered you: Check that you have not changed "View". The Notebook Layout is simply a normal document with specific formatting, a...

Multiple IIf statements
I'm having trouble placing multiple Include If (IIf) statements in a query. There are a number of organisms with full names in our database, I want to pull them but have them all displayed under the most generic of their names. The following statement works fine up until the second IIf. When I add that segment, I get a message saying I there is a syntax error or I have not included enough brackets. Org_1 Change: IIf([Org_1] Like "ESBL*","ESBL",[Org_1]),IIf[Org_1] Like "CR*","Carbapenem",[Org_1]) The mistake is probably obvious, but could so...

I would like to design a logo for a new superstore
I just can't seem to get an inspiration to design a logo to use for this superstore. Even coming up with a name seems to be a difficult task. Inspiration here, over 7,000,000 hits http://images.google.com/images?q=logo&hl=en -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "tinard" <tinard@discussions.microsoft.com> wrote in message news:CE8F1689-4D1A-401C-BE11-2F2DB3CF34A6@microsoft.com... >I just can't seem to get an inspiration to desig...

IIF statement??
I was going to use Payroll ID numbers as the unique identifier for each person that I enter into a database template that I recently downloaded, but realized on Friday that that may not work as we have some folks to be put into the database who do not have ID numbers. I would like to use IDs for those who have them, and then have the database randomly or sequentially generate them for those that I do not have IDs for. Would an IIF statement be the best way to do this? And if it is, how would I go about doing it? Thank you, Teri. Hi Teri, I'd be more inclined to add...

IIF
I have a field with yes/no, i would like to send an email if the field = yes, i have done a macro to send the document to a member of staff, but all the time, i want it sent when the field - yes only. Any ideas? Thanks Elaine On Fri, 09 Nov 2007 01:35:58 -0800, Elainie <Elaine.Macintyre@bsc.wales.nhs.uk> wrote: >I have a field with yes/no, i would like to send an email if the field >= yes, i have done a macro to send the document to a member of staff, >but all the time, i want it sent when the field - yes only. Just what are you sending? A report? A programmatically gener...