ADO Find method

I would like to know if there is a way to use the ADO Find method to find one 
of several values for a single column.  See the following example:

rstOpenJobProcess.Find "[proc#] = '940' or '941'"

I would like to search for proc# '940' or '941'.  I want to avoid using a 
like statement in the criteria (such as [proc#] Like '94*') because I may use 
'942' for something else in the future that I would not want to search for.  
Is there a way to use the Find method in this way?  Alternatively, is there a 
clever workaround?

Thanks,

Keith
0
Utf
1/28/2010 5:35:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
643 Views

Similar Articles

[PageSpeed] 37

"Keith" <Keith@discussions.microsoft.com> wrote in message 
news:CF416F20-AEA6-4774-920E-95E16370D669@microsoft.com...
>I would like to know if there is a way to use the ADO Find method to find 
>one
> of several values for a single column.  See the following example:
>
> rstOpenJobProcess.Find "[proc#] = '940' or '941'"
>
> I would like to search for proc# '940' or '941'.  I want to avoid using a
> like statement in the criteria (such as [proc#] Like '94*') because I may 
> use
> '942' for something else in the future that I would not want to search 
> for.
> Is there a way to use the Find method in this way?  Alternatively, is 
> there a
> clever workaround?


Try this:

    rstOpenJobProcess.Find "[proc#] In ('940', '941')"

That's assuming that [proc#] is a text field, as your original code 
suggests.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
1/28/2010 6:27:35 PM
Dirk,

Thanks so much for the response.  This looks like exactly what I'm asking 
for.  Unfortunately, when I use this code, I get the following error message:

"Arguments are of the wrong type, are out of acceptable range, or are in 
conflict with one another."

[proc#] is a text field as you assumed, so that's not the issue.  

I have figured out a workaround, basically using the find method twice and 
determining which result is what I want.  It's clunky, but it will work.  If 
you have any other thoughts, please let me know.  I could see this coming up 
again in the future.

Thanks again,

Keith


"Dirk Goldgar" wrote:

> "Keith" <Keith@discussions.microsoft.com> wrote in message 
> news:CF416F20-AEA6-4774-920E-95E16370D669@microsoft.com...
> >I would like to know if there is a way to use the ADO Find method to find 
> >one
> > of several values for a single column.  See the following example:
> >
> > rstOpenJobProcess.Find "[proc#] = '940' or '941'"
> >
> > I would like to search for proc# '940' or '941'.  I want to avoid using a
> > like statement in the criteria (such as [proc#] Like '94*') because I may 
> > use
> > '942' for something else in the future that I would not want to search 
> > for.
> > Is there a way to use the Find method in this way?  Alternatively, is 
> > there a
> > clever workaround?
> 
> 
> Try this:
> 
>     rstOpenJobProcess.Find "[proc#] In ('940', '941')"
> 
> That's assuming that [proc#] is a text field, as your original code 
> suggests.
> 
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
> 
> (please reply to the newsgroup)
> 
0
Utf
1/28/2010 6:56:01 PM
ADO recordset Find method support only one test. Use successive test on 
successive clones as workaround (http://support.microsoft.com/kb/195222) or 
use an SQL query which will pump the primary key value for your complex 
criteria and feed the ADO method with a test on that later.


Vanderghast, Access MVP


"Keith" <Keith@discussions.microsoft.com> wrote in message 
news:CF416F20-AEA6-4774-920E-95E16370D669@microsoft.com...
>I would like to know if there is a way to use the ADO Find method to find 
>one
> of several values for a single column.  See the following example:
>
> rstOpenJobProcess.Find "[proc#] = '940' or '941'"
>
> I would like to search for proc# '940' or '941'.  I want to avoid using a
> like statement in the criteria (such as [proc#] Like '94*') because I may 
> use
> '942' for something else in the future that I would not want to search 
> for.
> Is there a way to use the Find method in this way?  Alternatively, is 
> there a
> clever workaround?
>
> Thanks,
>
> Keith 

0
vanderghast
1/28/2010 7:35:49 PM
"Keith" <Keith@discussions.microsoft.com> wrote in message 
news:13907BE0-3416-4DC2-9310-5E1C5231F4D2@microsoft.com...
> Dirk,
>
> Thanks so much for the response.  This looks like exactly what I'm asking
> for.  Unfortunately, when I use this code, I get the following error 
> message:
>
> "Arguments are of the wrong type, are out of acceptable range, or are in
> conflict with one another."

Drat!  I'd hoped that phrasing the criterion as an In() expression would get 
past ADO's restriction on the Find method.  But no, it only allows certain 
comparison operators and a single field and value to be tested.

> I have figured out a workaround, basically using the find method twice and
> determining which result is what I want.  It's clunky, but it will work. 
> If
> you have any other thoughts, please let me know.  I could see this coming 
> up
> again in the future.

Vanderghast has pointed you to a KB article that may give you some 
alternatives.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

0
Dirk
1/28/2010 8:16:23 PM
Reply:

Similar Artilces:

Finding current printer page number
I have hunted for this without success. I want to know - programmatically - what page the active cell is in. More precisely, what page ActiveCell.Top is in. (I take it some sows can split into more than one page.) I want to automate putting manual page breaks into a sheet. I would put breaks at or before those automatically inserted by Excel. Thanks -- Walter Briscoe In message <KBH7nVN7dSuJFwvS@freenetname.co.uk> of Thu, 12 Mar 2009 15:17:15 in microsoft.public.excel.newusers, Walter Briscoe <wbriscoe@nospam.demon.co.uk> writes >I have hunted for this without success. >...

Can't find Product Key
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel My iMac came pre-loaded with Office. For no reason that I can find, I can no longer open any Office Application - it is asking me to put Office in the trash along with "Users/?" which I can't even see in Finder. And then to re-enter the Product Key. I have the 2 install disks, but have no idea how to find the product key. There are little white stickers with bar codes. Is the product key one of these number? HELP! It sounds as though the Office software might be a free trial version. When you open a progra...

Find the value of a point with VBA
Hi all, Pardon my english ! I'm french. I need to find the values of points of a serie (in charts of course) with VBA. I can't refer to the value of the source cells because of the very bad organization of the source sheets. The only way I found is to add datalabels, pick the datalabels values, convert them to numeric values. Is there a shorter method to do that ? (XL2K) I did not find the answer on the french speaking excel newsgroup though it's very active and frendly. Thanks -- Fran´┐Żois L Hi, This will get the values from series 1. Note that linked cells with erro...

CRM 1.2 Error Can't Find Custom Field
Here's the problem: Whenever I try to double-click on an account to open it, CRM throws an error saying check the application log. I check the app log and find an error saying CRM can't find one of my custom fields. The field mentioned is one of the last I put in. A few days ago I made a backup of a "clean" CRM database (no account or contact records) so I would have a starting point to restore from while testing a new migration process that would import records from a legacy system. Today after running a few tests I restored the db from that backup. Directly afte...

Using Excel to find age
I would like to use Excel and enter the days months and year in a cell of each persons birthdate. Then I would like to enter the todays date, year, and month so it will calculate the results and display how old the person is. I don't know if this is possible or not. I thought it would be nice to have Excel to calculate the results of a persons age. Any ideas how to accomplish this task? Chip has a good page on this: http://www.cpearson.com/excel/datedif.htm -- Michael Hopwood (Phobos) "Eric" <anonymous@discussions.microsoft.com> wrote in message news:DBE8E834-5CF9-46...

Any short key finding special cell?
Hi, I am new in excel and looking for ways to increase efficiency with it. I have a long spreadsheet, i am interested in some rows where col A is a specific value, says, X, for example Col A Col B Col C row 1 X B C .... row 100 X BB CC .... row 250 X BBB CCC Is there any ways in excel that i could use to go to row 100 directly? I am just wild guessing, is it possible to have a macro to access that row and assign a short key to it. For example, is it possible that, a marco M to find next row where c...

I am getting this error and cant find out why?
STACK: at System.Web.HttpApplication.MapHttpHandler(HttpContext context, String requestType, String path, String pathTranslated, Boolean useAppConfig) at System.Web.MapHandlerExecutionStep.System.Web.HttpApplication+IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Error Description: C:\Program Files\Microsoft CRM\CRMWeb\opps\areas.aspx Error Details: Exception of type System.Web.HttpException was thrown. Error Number: Source File: Not available Line Number: Not available ...

Changing costing methods
Our auditors want us to change our cost method from average to FIFO perpetual. Other than using the utility to change each item, what other considerations are there? Is there documentation on this? Thank you...... There is no real documentation but here is what you need to do. MAKE A BACKUP FIRST!!!!!!!!!!!!!!!!!!! 1. Make sure your stock status by account number balances to GL. 2. Post all sales orders that can be posted and delete the rest. 3. Receive all the POs you can and invoice them. Delete the rest. 4. Use the utility (it will not change items on Sales Orders or POs 5. ...

Item Replenishment & Item Fulfill Method
Does anyone know the table names that the above 2 fields are located in? I would like to add these to a smartlist so I can make sure they are all set correctly. The Replenishment Method is o nthe Item Resource Planning Maintenance window and the Fulfill method is on the Item Engineering data window. We do have manufacturing so I am not sure if they are manufacturing tables or not. thanks! -- Doug ...

Outlook Search/Find feature
Hello, As per an ongoing Audit policy. We need to look for specific words in some mailboxes. I tried the find? advanced find feature but could not find a way to look for an excat word. For instance: If I need to look for the exact word brooks, I don't need the search results to come up with holbrooks as well as brooks. Any ideas? Thanks in advance. Hi lara, maybe this program is a help Lookout V.1.2 on http://home.arcor.de/andreas.roeder-privat/OL_Prog.htm#18 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Dat...

Looking for client-side method (Outlook 2007/Exchange 2007) for users to delete
Good Morning, We have Outlook 2007 clients on Exchange 2007. Client side registry has dumpster turned on for all mailbox folders. Exchange is configured with 21 days dumpster retention. This configuration is working well for us. But we also would like to give specific users, the ability to quickly and easily purge the contents of all their folder dumpsters, at their discretion. These users do this now, but it is all manual. They use the 'Recover Deleted Items' dialog and permanently delete the contents of the dumpster from there, but this is only possible on o...

ADO #4
Hello , i am working on a project which is maintaining the transportion for a call center company where i have to have a ADO data bound dialog on which i have place a combobox control which should have a access to database recordset field to view that field data has a dropdown in this combobox control . so that when ever the table with this field gets updated the combobox data has to get update automatictly . Thanks ...

SelectSingleNode not finding nodes
I have the following Visual Basic .NET code in an ASP.NET project: ' ... Dim xdStructure As XmlDocument Dim xnStructureRoot As XmlNode xdStructure = New XmlDocument xdStructure.Load(Server.MapPath("~/xml/Structure.xml")) xnStructureRoot = xdStructure.DocumentElement If xnStructureRoot Is Nothing Then Throw New Exception("No document element found in Structure.xml.") End If ' Get application node. xnApplication = xnStructureRoot.SelectSingleNode("application[@tag='"...

Need help finding non-duplicates email addresses...
Hi, I have a list of email addresses which I would like to find only the new email addresses within the list. Inside of my Excel worksheet I have a row of e-mail addresses. Some of the names are duplicated which tells me they already exist somewhere in the row. I wish to display only the new names. Can you tell me how this can be done in Excel? Thanks in advance for your assistance, Greg Hi, Select the row. GoTo conditional formatting, Select Formula-Is option in first combo, enter =COUNTIF(1:1,A1)>1 in second box. Select format and select suitable font,colour etc. Click OK. Every repe...

Advanced Find doesn't work
After having updated my Office 2000 version with service release 1a and service pack 3 the Outlook 2000 advanced find function no longer works. It doesn't deliver any results and pretends to continue searching forever. Anyone any ideas about this problem? Thanks, Steve Same problem here....except it doesnt seem to keep searching. It will only find something within the last day or so. "Steve Kraft" <anonymous@discussions.microsoft.com> wrote in message news:00a501c3c472$93d71c90$a601280a@phx.gbl... > After having updated my Office 2000 version with service > rele...

Weird Finding
I have a formula set up to calculate the difference between 2 times. Cell A2 = 10:00 AM Cell B2 = 10:25 AM The formula in Cell A3 is =(B2-A2)*1440. This formula returns the total minutes between the two times into Cell A3 which the value is 25. I have conditional formatting set up to fill in the cell color as red if the value is less than 25. For some reason, the cell is filling in red for the calculation between 10:00 AM and 10:25 AM which is incorrect due to the value being exactly 25. However, if I do a calculation of 11:00 AM to 11:25 AM, the cell with the conditiona...

Find cell value in another .CSV file
Hello everyone, I've another challenge; two .CSV file one is named first.csv and th other second.csv. Column A of first.csv contains the account number and each account number is unique. Second.csv column A has again the account number but can be repeated a it is the record of the activity of the accounts. Each account numbe has four columns of other information. What I want to do is get the info on second.csv and add that 4 colum of other info on the corresponding row of that particular accoun number in first.csv. Sample first.csv Acct_No Beg_Amt Name R12345 420.23 My...

Finding Used and Unused Numbers
Hi, I have a worksheet that contains 2 columns of data: Column A contains a list of all available numbers to use. Column C contains a list of all numbers that have been used. I need to be able to find out the following: Which numbers from Column A haven't been used. Which numbers from Column A have been used twice. Thanks! Hi in column B enter the following formula =COUNTIF($C$1:$C$1000;A1) and copy down -- Regards Frank Kabel Frankfurt, Germany Glenn wrote: > Hi, > > I have a worksheet that contains 2 columns of data: > > Column A contains a list of all av...

How to find number of selected files in CFileDialog
hi friends, if any one can tell me how to find the no. of selected files in CFiledialog before traversing the whole list of filename through POSITION data structure. Regards ravi in short, no. But why not just traverse the list once keeping a count then traverse again and do whatever you need to with the selections. I presume you need the total in order to prime some other variable. BTW if you're worried about the overhead of traversing twice, forget it. In comparison to the time it took the user to click on the files its' miniscule. Even compared to the time it took for ...

How do I find out if I have Office 2000
How do I find out if I have Office 2000 on my computer? It is a Compaq 1700T. I bought it in August, 2001 Can you open Word or Excel? If yes, then Help|about should give you some indication of the version. But Office (any version) isn't bundled free with any computer (as far as I know). You have to pay for it separately (it isn't part of Windows (any version)). Agnes wrote: > > How do I find out if I have Office 2000 on my computer? It is a Compaq > 1700T. I bought it in August, 2001 -- Dave Peterson Start>Run and type in Excel and press enter, then click he...

I cannot find hatched/transparency option for objects in Ppoint
Can anybody advise how to apply a hatch fill to an object and also adjust the transparency of the fill. I could do it in 2003 version but am struggling with 2007. Thanks Pattern fills were removed in PPT 2007. (They're back in PPT 2010, though.) -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/32a7nx "Brian Mooney" <Brian Mooney@discussions.microsoft.com> wrote in message ne...

::: Excel
Hello, Is there any formula that gives you the last number of a column ? Based on that number can I found the data related to it (from the same line) ? My questions are detailed into this Excel file : http://cjoint.com/?ewkEgKqJeI Thanks for your help. Jacques =LOOKUP(10^99,A:A) or =LOOKUP(9.99999999E+307,A:A) Use any number that you know will be bigger that the biggest number in your data. 9.99999999E+307 is the largest number you can put into a cell. But 10^99 is easier to type. infojacques@gmail.com wrote: > > Hello, > > Is there any formula that gives you the last num...

How Can I find and replace symbols in excel data ( white square)
I need any input or help Hi try: http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Regards Frank Kabel Frankfurt, Germany alawhizkid wrote: > I need any input or help ...

Using client PC to find the functnl level of my log-in AD domain
Using Windows XP service pack3 to login to AD domain. Running the GPresult from the command prompt returns the following: RSOP results for TMS\TABATAJ on PD9403355 : Logging Mode --------------------------------------------------------- OS Type: Microsoft Windows XP Professional OS Configuration: Member Workstation OS Version: 5.1.2600 Domain Name: ABC Domain Type: Windows 2000 Site Name: DATA-HUB-SITE Roaming Profile: Local Profile: C:\Documents and Settings\tabataj Connected over a slow link?: No -------------------- Can I conclude that the Functional level ...

Method to group lines
Hi, I have this kind of sheet: 1?? AA1 2?? BB1 3?? CC1 3?? CC2 3?? CC3 3?? CC4 3?? CC5 2?? BB2 3?? CC6 3?? CC7 3?? CC8 1?? AA2 2?? BB3 3?? CC9 3?? CC10 3?? CC11 every rows it's a cell. The first number is the level of depth. How can I group group the rows by the level? I need to use the function "Group" of Excel, so I can hide/show rows. In this way I would see, before all the 1 then I expand them and I see only the 2 and so on... Thanks in advance for your help! -- Ciao BlackSun I usally add 3 auxilary column to the worksheet.. these could ...