IF ISNUMBER FIND in query design expression

I use the following formula in Excel and it works...I now want to do this in 
a query.  Can I add it as an expression in the design grid in a seperate 
column and how does the syntax change?  I will also want to use TRUE/FALSE 
for the result.
=IF(ISNUMBER(FIND("TRANSF",H2)),"*", "")
0
Utf
5/27/2010 9:14:13 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1559 Views

Similar Articles

[PageSpeed] 24

Try this --
  My_Output:  IIF(InStr([YourFieldName],"TRANSF")>0,"*", "")

-- 
Build a little, test a little.


"gator" wrote:

> I use the following formula in Excel and it works...I now want to do this in 
> a query.  Can I add it as an expression in the design grid in a seperate 
> column and how does the syntax change?  I will also want to use TRUE/FALSE 
> for the result.
> =IF(ISNUMBER(FIND("TRANSF",H2)),"*", "")
0
Utf
5/27/2010 9:56:55 PM
Reply:

Similar Artilces:

Sorting in Queries
Hi, I am trying to write a query that assigns contributions for each account considered to either East or West depending on which territory has the larger contribution. I am wrote a queryStep1: SELECT [tbl_Transactions].month_end_date, [tbl_Transactions].account, [tbl_Transactions].contribution, [tbl_Transactions].territory FROM [tbl_Transactions] ORDER BY [tbl_Transactions].account, [tbl_Transactions].contribution DESC , [tbl_Transactions].territory; Then wrote queryStep2: SELECT step1.month_end_date, step1.account, Sum(step1.contribution) AS SumOfcontribution, Firs...

Problem with xpath query
Hi! Here's a XML reprensenting a company. I would like to make a XPath query that gives me all the employees that are Programmer or Analyst and have 5 years of experience. I did the following query "//*[local-name()='Employee']/*[(local-name()='Position' and (text()='Programmer' or text()='Analyst'))]//parent::*/*[(local-name()='YearsOfExperience' and text()='5')]//parent::*" and it gives me 4 nodes. The 2 I need and one for Position and another for YearsOfExperience. Please help me to get through this! I'm using VB.net and ...

Buttons on Outlook Express
The buttons at the top of my Outlook express keep expanding, getting wider and wider. I have placed 8 buttons that I want to use but for some reason, it keeps changing as the buttons get wider first there are 8 then there are 6 and then 4 then 3 then 2 really wide buttons. Does anyone know how to stop this insanity? ...

calculations in a query
Hi there I am doing a query with calculations and the 2nd set is not working. The calculations are as follows: 1LoadTime: DateDiff("h",[starttime],[Finishtime]) This is done for 3 ports -- all the same formula and it works. But then I do a formula to sum the 3 ports as follows: TotalTime: ([1LoadTime] + [2LoadTime] + [3LoadTime]) and it doesn't work. There is no error message. It just shows a blank where a number should be. Please let me know how to make it calculate properly -- Thank-you! Ruth TotalTime: Nz([1LoadTime],0) + Nz([2LoadTime],0) + Nz([3LoadTime],0) ...

Query Refresh
I have a worksheet that has an external data source that refreshes automatically. Each time a user opens this workbook, they receive a dialog prompting whether or not they trust the source of the workbook. Their option is to Enable automatic refresh or disable automatic refresh (default). I dont want this to appear. The worksheet should automatically, without user intevention, enable auto refresh. How do I do this? Thanks Mike ...

How can I count unique values in a query in the report footer
Am using Access 2003 I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the repo...

Where can I find all of the SA functionality
I'm doing a project for SOX compliance and I need to document all of the functionality that SA has in 7.5 that can't be assigned to a regular user. Where could I locate that information? SA by default has complete access to the systems, expect where you explicitly revoke access under individual user security. SA is hard coded in the application to create new users, and assign user access to companies. The best bet is to contact MBS support for this documentation if they have any >-----Original Message----- >I'm doing a project for SOX compliance and I need to >...

Query Date Help W/Avg work days
please note that in order to get the date 6/28/1979 I am using average work days each month of 21.75 I have tried to Add just the total days to the date but it's not correct because I need it to be based off the 21.75 days per month not actually days. Total Years of Prior Service Days Hire Date Adjusted Hire Date 7 years 4 months 6 days 2685 6/15/1987 2/7/1980 (s/b 2/9/80) 1 year 8 months 13 days 620 2/10/2005 6/1/2003 (s/b 5/27/03) This is what is being calculated so far once I run the query. "aldunford" wrote: > This worked great thanks so much!! I have ...

how to get info from outlook express to outlook
I HAVE JUST BOUGHT A NEW COMPUTER. ON MY OLD COMPUTER I WAS USING OUTLOOK EXPRESS AND NOW I WANT TO USE MICROSOFT OUTLOOK ON MY NEW COMPUTER. IS THERE ANY WAY I CAN GET MY INFORMATION OFF MY OLD ONE TO MY NEW ONE. I HAVE FOLDERS SET UP IN OUTLOOK EXPRESS WITH SEVERAL DIFFERENT EMAILS IN THERE. I DIDN'T KNOW IF THERE WAS A WAY TO GET ALL M INFORMATION TRANSFERRED TO MY NEW COMPUTER FROM MY OLD ONE. PLEASE HELP. THANKS BECKY You need to fix your more serious problem, the broken caps lock key, before we can help you with your question. "becky" <bsba@watchtv.net&g...

Finding the last row in a spreadsheet
Hi all. I'm a relative newbie to using Excel and am stumped. I'm trying to find the last (non-zero) value in a column. The cells all have a function in them presently like =IF(C8>0,SUM(B8:C8)," ") presently so that the cell truly is not empty. I've tried using =LOOKUP(2,1/(D2:D350>0),D2:D350) but it displays nothing since all the cells have a value (the function above). How do I find the last cell having a numeric value (which is what the first function provides)? Any help is greatly appreciated. It's because you don't use empty as opposed to space &...

Workflow - date and time
hi I have a field called enrolment date which is usually lesser than the created on date. i want a workflow which says, when a new contact is created wait for 5 days after enrolment date and then create - 1st mailer task wait for 2 months and create - 2nd mailer task. i have got a workflow and it says when a new patient is created wait 5 days after contact.enrolmentdate if case 1 create mailer 1 end if if case 2 create mailer 2 end if however, both mailer tasks get create right away rather than waiting for 5 days and 2 months. in the wait for timer i ticked revaluate expressision whe...

after moving mailboxes, outlook find feature issues
After I moved mailboxes from our Exchange 2000 server to our Exchange 2003 server I noticed that when you do a "find now" to search for messages in Outlook it starts at the date the mailbox was moved, instead of the most recent date. How do I adjust it so it searches the most recent date first? On Fri, 12 May 2006 10:39:01 -0700, todd5574 <todd5574@discussions.microsoft.com> wrote: >After I moved mailboxes from our Exchange 2000 server to our Exchange 2003 >server I noticed that when you do a "find now" to search for messages in >Outlook it star...

Querying XML data
Hello, Using SQL 2008 I'm taking xml string from a VARCHAR(max) datatype column and inserting into XML datatype column then want to query it. Doing the above like so: DECLARE @XmlSourceTable TABLE (RecordId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, XmlData XML NOT NULL ) INSERT INTO @XmlSourceTable (XmlData) SELECT TOP 1 CONVERT(XML,REPLACE(REPLACE(CSR, '&lt;', '<'), '&gt;', '>'),2) FROM CDATA The xml looks like this after inserting here is just a small part: <SoapGetCSRReturnResponse xmlns="http://temp...

MS Query
This is a multi-part message in MIME format. ------=_NextPart_000_001E_01C3675C.64744720 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Does anyone know where an SQL beginner can get basic help with commands = in MS Query. I have limited knowledge of SQL and want to be able to create my own = Queries in MS Query but as usual the help isn't. Web pages, news Forums etc.... I'm also after a job if anyone wants to know! Regards Dean=20 dkso@ntlworld.com=20 http://homepage.ntlworld.com/dkso=20 --- Outgoing mail is certified Viru...

How do work web DNS queries when there is no ISP DNS given
Thanks for all the help I have already received from this group members. How DNS is resolved when there is no ISP DNS servers listed in SBS 2008 settings yet? An built in external MS DNS connection? Are lthen ocal ISP DNS entries even essential, if web access is granted by built in external DNS connections and where is correct to add local ISP DNS? I found several possible locations: 1. I have found in literature - Local server DNS properties Forwarders 2. IPv4 Scope Options -> DNS servers by default local SBS IP but I have seen there added extra ISP DNS numbers. And ...

Outlook Express Filters
Where are the filter settings in Outlook Express saved? Are they in the registry and if so where? Matt This newsgroup is for support of Outlook 97-2003 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x http://support.microsoft.com/newsgroups/default.aspx?ICP=GSS3&NewsGroup=microsoft.public.windows.inetexplorer.ie5.outlookexpress&SLCID=US&sd=GN&id=fh;en-us;newsgroupsmicrosoft.public.win...

Macro
I have a Macro which finds a unique value in a sheet, but I then want it to move down 6 rows from where it finds that value to do something else. What is the code to 'move' around by x number of cells ? -- http://www.redbrick.dcu.ie/~pele You can use .offset() from the cell you found: Option Explicit Sub testme01() Dim FoundCell As Range Dim FindWhat As String FindWhat = "asdf" With ActiveSheet.UsedRange Set FoundCell = .Cells.Find(What:=FindWhat, _ After:=.Cells(.Cells.Count), _ ...

manipulating data from Find dialog in web browser ActiveX control.
Hello everyone, I would like to know how can I get access from MFC to the text that was entered in the Find dialog of the Microsoft web browser ActiveX control. Documentation is pointing to IHTMLOptionsHolder interface but I cannot get reference to it (I am getting E_NOINTARFACE) from any of the available interfaces in my code. Note, I do not have problem to query other standard interfaces exposed by ActiveX control (ex.: IHTMLDocument2, IOleCommandTarget, and IDispatch). I would appreciate any help regarding this matter. Best Regards, Andrew ...

Query Help 05-09-07
I have a Query with 5 Tables in it. tblRestInfo tblVoids tblItems tblVoidReasons tblEmployees When I have the tblVoids alone I get all of the information but when I add the other tables to fill in the information I get nothing. Here is my code. SELECT tblINFO.RestaurantName, tblINFO.RestID, tblChecks.ChkAlias, tblVoids.VoidDate, tblVoids.VoidTime, tblVoids.VoidQTY, tblItems.ItemName, tblVoids.VoidAmount, tblVoidReasons.VoidName, [EmpFirstName] & " " & [EmpLastName] AS MAN FROM tblINFO, (((tblVoids INNER JOIN tblItems ON tblVoids.VoidItemID = tblItems.ItemID) INNER JO...

OWA contacts, can't do a find on DLs
Hi, just deployed OWA 2k3 and when i go to "To" click contacts and then try to find a DL it will not show (personal contacts not Global) what do you think is wrong thx monica Techie In Need <moni6422@yahoo.com> wrote: >just deployed OWA 2k3 and when i go to "To" click contacts and then try >to find a DL it will not show (personal contacts not Global) what do you >think is wrong Nothing's wrong with Exchange. Right-click the Contacts folder and display its property page. Click the Outlook Address Book tab and check the box lableled "Show thi...

Hours for month as value in query
Hi Everyone, I don’t know if this is a simple or complex request but it’s definitely complex for me. I want to have a field in my query that does the following: If Month has 5 Fridays, Then value=200 If Month has 4 Fridays, Then value =160 I would assume that I’d need a parameter to ask for the month when I open the query. I’m going to use this field in a calculation with another field. Can anyone help me with this or point me in the right direction or am I assuming too much here as far as the capabilities of Access? Any suggestions will be greatly appreciated. Kay ...

Outlook 2003 Querybuilder doesn't find everything it is supposed to find
I have solved part of this problem myself, but need help on the rest. Outlook has a slick feature called querybuilder which enhances advanced find significantly (see http://support.microsoft.com/?kbid=307922). But, if I use advanced find to see every message from John I see 15 messages. If I use query builder, I only see 10. After some reseach, I discovered that I had to tell query builder to look for two things: from contains John OR HaveRepliesSentTo contains John. But this doesn't always work. I have another e-mail where advanced find gets it, but query builder doesn't. When I...

FIND the BEST MORTGAGE RATES,mortgage calculators,Second mortgage
http://www.webfreeguide.com/Mortgage-Brokers.aspx Mortgage Brokers Directory has a large list of mortgage companies in the United sates,UK,Canada and Australia.you can find the best mortgage rates,mortgage calculators,mortgage refinancing sources,second mortgage,mortgage quotes,bad credit mortgages and lot's of other online mortgage informatoin here. http://www.webfreeguide.com/Mortgage-Brokers.aspx ...

How to reduce Excel worksheet view and find horizontal scroll bar
I can't see the bottom of my Excel workbook including the tabs and the scroll bar. What can I do to fix it. Carolyn, Perhaps:- Tools|options|view check - sheet tabs and horizontal scrollbar. Mike "CarolynC" wrote: > I can't see the bottom of my Excel workbook including the tabs and the scroll > bar. What can I do to fix it. First of all.........under Tools>Options>View are sheet tabs and scroll bars enabled? If so, is the window maximized? Can you grab the Title bar and move the window up? If not, try Window>Arrange. Gord Dibben MS Excel MVP ...

Finding only a directory in CFileDialog
Is there any way to tell a CFileDialog object to accept directories as valid selections when traversing a directory tree? Or is there another class for this kind of dialog? I've seen such a thing in some applications but it may be a custom class. TIA, Lilith ShBrowseForFolder joe On Tue, 31 May 2005 21:25:27 -0500, Lilith <lilith@dcccd.edu> wrote: >Is there any way to tell a CFileDialog object to accept directories as >valid selections when traversing a directory tree? Or is there >another class for this kind of dialog? I've seen such a thing in some >ap...