Find Text in a Query

Does anyone know of a way to search thru AC2003 queries to find a text 
string?  For example, I need to find all queries that contain the text 
"Forms".

Thanks for your help!
0
Utf
3/6/2010 10:10:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
4285 Views

Similar Articles

[PageSpeed] 15

On Sat, 6 Mar 2010 14:10:01 -0800, PeterM <PeterM@discussions.microsoft.com>
wrote:

>Does anyone know of a way to search thru AC2003 queries to find a text 
>string?  For example, I need to find all queries that contain the text 
>"Forms".
>
>Thanks for your help!

There might be a way to use the systems tables, but I wasn't able to figure it
out. Try:


Public Sub SearchQueries()
Dim q As QueryDef
For Each q In CurrentDb.QueryDefs
   If InStr(q.SQL, "Forms") > 0 Then
    Debug.Print q.Name, q.SQL
    End If
Next q
End Sub
-- 

             John W. Vinson [MVP]
0
John
3/6/2010 11:43:29 PM
http://www.rickworld.com/products.html

-- 

AG
Email: npATadhdataDOTcom


"PeterM" <PeterM@discussions.microsoft.com> wrote in message 
news:2E969E10-6EBF-41B3-8207-AD8312CF6483@microsoft.com...
> Does anyone know of a way to search thru AC2003 queries to find a text
> string?  For example, I need to find all queries that contain the text
> "Forms".
>
> Thanks for your help! 



0
AG
3/7/2010 1:50:36 AM
This seems to work:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = 
MSysQueries.ObjectId
WHERE (((MSysQueries.Expression) Like "*forms*") AND 
((MSysObjects.Type)=5));

It will return "internal" queries (recordsources for forms, combo-boxes, 
etc, which have a SQL statement); if you want to exclude those, use this:
SELECT MSysObjects.Name, MSysQueries.Expression
FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = 
MSysQueries.ObjectId
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like 
"*forms*") AND ((MSysObjects.Type)=5));

HTH,

Rob


PeterM wrote:
> Does anyone know of a way to search thru AC2003 queries to find a text
> string?  For example, I need to find all queries that contain the text
> "Forms".
>
> Thanks for your help! 


0
Rob
3/7/2010 2:42:05 AM
John, AC and Rob... thanks for your answers, works like a champ!

PeterM

"Rob Parker" wrote:

> This seems to work:
> SELECT MSysObjects.Name, MSysQueries.Expression
> FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = 
> MSysQueries.ObjectId
> WHERE (((MSysQueries.Expression) Like "*forms*") AND 
> ((MSysObjects.Type)=5));
> 
> It will return "internal" queries (recordsources for forms, combo-boxes, 
> etc, which have a SQL statement); if you want to exclude those, use this:
> SELECT MSysObjects.Name, MSysQueries.Expression
> FROM MSysObjects INNER JOIN MSysQueries ON MSysObjects.Id = 
> MSysQueries.ObjectId
> WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysQueries.Expression) Like 
> "*forms*") AND ((MSysObjects.Type)=5));
> 
> HTH,
> 
> Rob
> 
> 
> PeterM wrote:
> > Does anyone know of a way to search thru AC2003 queries to find a text
> > string?  For example, I need to find all queries that contain the text
> > "Forms".
> >
> > Thanks for your help! 
> 
> 
> .
> 
0
Utf
3/7/2010 6:00:01 PM
kl,,,,,,cccc

"PeterM" <PeterM@discussions.microsoft.com> a écrit dans le message de 
groupe de discussion : 2E969E10-6EBF-41B3-8207-AD8312CF6483@microsoft.com...
> Does anyone know of a way to search thru AC2003 queries to find a text
> string?  For example, I need to find all queries that contain the text
> "Forms".
>
> Thanks for your help! 

0
joelgeraldine
3/17/2010 1:37:42 PM
Reply:

Similar Artilces:

Cannot find the specified Microsoft Exchange Server
This is the error we get upon the final install checklist. ? What can cause this? Our Network Admin is pretty confident that all of his settings are fine. We can see the Exchanger Server in the domain. What are we missing? There are no users set up yet on this test Eschange server, but there is a OU setup on the Domain...but no specific user there either.????? ? If we press ahead with an install, we can add Exchange (and the router) later, correct? yes you do not need to specify an exchange server to complete the install. You can always add it later ============================== Jo...

Finding templates and formats
Can you please tell me, where can I find a template or the format of a resignation letter? With much appreciation. Come on Mary...let's see you find a template for this poster :-D DavidF "mandm" <mandm@discussions.microsoft.com> wrote in message news:724ADDB2-F04E-4AA7-AD35-59BD7CF9ECE7@microsoft.com... > Can you please tell me, where can I find a template or the format of a > resignation letter? With much appreciation. Here's an idea, http://www.azlyrics.com/lyrics/deadkennedys/takethisjobandshoveit.html -- Ron "DavidF" <Nope@nospam.com>...

I have to find the sender's email address
I have to find the sender's email address. But oMsg.SenderName -- > Sender Name returns the name. How can I find the email address? Thanks. oMsg.SenderEmailAddress -- Remember, the object browser (F2 in VBA) is your friend. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "win" <wintelreport@gmail.com> wrote in message news:5992e67c-6655-4638-bf58-6ca1d60a52a4@21g2000yqj.googlegroups.com... >I have to find the sen...

Merge different queries
Hello all, I have two tables where I enter my stock trades as well as foreign exchange (FX) trades. The two tables contain different fields as the trading instruments require different entries. I then created two seperated queries in order to calculate the profit from each stock and each FX trade. I need two queries as the calculations are different, too. Now, I would like to merge the two queries in order to see my overall profit and losses no matter if the trade is a stock or FX trade. That is, in the final query I would like to see the trade ID (which is the primary key in eac...

Finding a mailbox (Connector) ??
Below is a line I took from our Backup Exec server log. "Microsoft Exchange Mailboxes\Internet Mail Service (xxxxxx) [internet mail connector (xxxxxx)]MTS-IN Folder00008" I´ve tried everything to find this particular mailbox but failed. I know this is also our connector to the outside and I checked the physical folders where mail are stored but nothing is there. In the administrator tool I cant find it either in hidden or non hidden boxes wich makes it impossible for me to find the faulty email inside. I cant open it in Outlook either since it cant find that particul...

Checkbox to a query
I have a form with a checkbox; I'm trying to build a query that grabs that value from the checkbox (-1), and use it as a criteria. In the criteria, I want to say: if the checkbox is checked, give me all records that do not have a -1, otherwise give me all records. So to clarify: I have two tables. first one is "new table" and the second one is "archive table". I want to build a query, so that if the checkbox is checked, all records in the "new table" that are NOT in the "archive table" will be shown. If the checkbox is not checked, then I want all...

Reply marking in original email text
I can't find how to turn on the reply marking (ie my name) when replying to an email by inserting the reply within the body of the original email. I am using Outlook 2000 TOOLS menu Select OPTIONS On the PREFERENCES tab Click the E-MAIL OPTIONS... button Near the bottom of thid window is a little box to turn on next to "Mark my comments with:" -- Nikki Peterson [MVP - Outlook] "Paul Barton" <paulb@NOSPAMcae.com.au> wrote in message news:%230g6OU%230DHA.2604@TK2MSFTNGP09.phx.gbl... I can't find how to turn on the reply marking (ie my name) when replying...

How do I copy an Excel chart with added text boxes into Word or P.
I have created a chart in Excel and added a number of text boxes on top of the chart. I need to paste the entire chart & text boxes into Word and/or Powerpoint; however by selecting the chart and copying, the text boxes are excluded. MS help suggests entering the text boxes outside the chart then moving them into the chart and grouping the chart and text boxes; however there does not seem to be the ability to group the chart with the text boxes. If you had clicked on the chart before making the textboxes, all the items would have been grouped. But you can do it later: Hold down SHI...

query problem 12-01-09
I've got a table of Organizations and a table of Memberships. Memberships have an OrgID and one Org can be part of many Memberships. Memberships have an expiration date. I'm trying to build a list of Organizations that have at least one non- expired Membership associated with it. I'd like each current Org to show once in a list (for an unbound combo box). I tried: SELECT tblOrganizations.OrganizationID, tblOrganizations.Name, [tblMemberships.Anniversary]>(Date()-60) AS [Current] FROM tblOrganizations INNER JOIN tblMemberships ON tblOrganizations.OrganizationID ...

Highlighting text using conditional formatting
Hi All I'm trying to highlight certain text in a list - namely all text that end with river, stream or creek. I've been trying to use conditional formatting but for some reason I can't seem to get it to go (I would have thought * wildcard would have done the trick but no such luck). Ideally you would have a different colour for rivers, creeks and streams. The list is like this Grey River Brash Creek Fred Stream Hell Stream Roger River Turd Creek and so on (about 3000 entries) Any ideas about how you can do this? Feedback appreciated. Thanks Mike Try this... Select the text ...

Powerpoint Placeholder Text Box
I have a series of template pages with multiple text boxes. I would like all the text boxes to auto fit consistently if one needs to. It works like that on a two box that is in the basic package, but when I created a 3 box and a 4 box, they each auto fit based on the text in the individual box. I've tried grouping them in slide master, and normal view. Any ideas? ...

Search/Extract Data w/in Text File
Hello again! I am searching within a text file for the last row within the file. After I locate the row I need to separate the data and place into multiple cells on my worksheet. The code I have is as follows: Dim TextPath As String Dim DelimSrchChar As String Dim METtime As String Dim DataBuffer As Variant Dim NCData As Variant Dim DelimPos As Integer '=============================================== DelimSrchChar = "," TextPath = "C:\Temp\Formatted_NC_SN1008.txt" Open TextPath For Input As #1 'open text file for SN1008 Do While Not EOF(1) ...

Advanced Find Results Export Failure
Hi all, I am seeing problems exporting results from Advanced Find to Excel. Sometimes, when the user clicks on the Excel icon, the program just seems to hang at a window showing "project_data.aspx to crm" and the transfer icon (showing files flying across from one folder to another), but the action does not get completed. This appears to be pretty random, it works once, but then not the next time, for the same Saved View. Could this be a Security Role related setting? Or is this related to the server? Does anyone have any suggestions? Thanks in advance. - Smita Thought I'...

Popup a message when query finished
I have a form that has a command button with code that updates 7 tables by a series of 7 queries. Is there any way to popup a message box to indicate to the user that all the queries have run successfully and they can move on? Although it happens very quickly there is no visual evidence to the user that the query is running or finished. Thanks Tony -- Why don't my grey cells communicate with each as fast as they used to? I hate getting old! Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1 Yes, you can put a Msgbox...

Advanced Find Gripe 3
When searching in Advanced Find for Accounts, we select the field "Hospital Name" which is our primary account and then the Condition "contains" and then the value "North Hospital". We clicked go we had only one record pull up even though there are hundreds of hospitals with North & Hospital in the hospital names. So we tried putting "North * Hospital" in to the value and we got a lot more. Are there additional wildcards or boolean operators and if so where do we find the information? ...

May I post here questions about SQL queries against Excel via ODBC?
and if not, can you recommend of a relevant forum? For example, I'm desperately looking for an answer to the question: How can I retrieve the row number of a record in a query? Somethin like: select x.*,rownum() from sheet1$ as x Thanks in advance, Noam ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com try posting in the Security Forum, under SQL Server Security, that's where most people go when they have questions about SQL. ...

Text From User Forms
Hi I am creating a user form where data is entered into text boxes. Once the operator has input data into a text box I want to use a command button on the user form to save the file. One text box will have a request date entered as the value and another will have the name of the requestor as the value. I want the command button to create the filename as these two values. Could someone offer some advice please. Thanks. Matt Matt, Activeworkbook.SaveAs filename: ="C:\your path\" & Textbox1.Text & " " & Textbox2.Text HTH Henry "Matt G" &l...

Where do I find "save as..." in MS Office 2007 ?
Sorry, but I did not found "save as..." menu in Office 2007. I only found in the upper left corner a flopy icon which represent a "save" but not a "save as...." So where is "save as..." (with files selction dialog) hidden in Office 2007? Camille Click the Office button (the round "pizza icon" in the upper left corner of the screen) and then click Save As. -- Stefan Blom Microsoft Word MVP "Camille Petersen" <cpet_magnus@hotmail.com> wrote in message news:4baf94cc$0$6768$9b4e6d93@newsspool3.arcor-onlin...

Problem with Microsoft Query
It doesn't return some of the data. It returns all but one actually. And I went into Microsoft Query help and followed the instructions. The file is closed, and the data is formatted the same way. Any advice on how to fix it? Jul, Your question is extremely vague. You should probably provide more explicit information about the details of your query such as The type of file are you trying to query The program you are using to perform the query Details of the query parameters Details of the information that is not being retrieved Also, make sure you change the Subject of your messag...

Finding Oracle Database
Hello, I want to link Oracles tables to an Access DB but can't see them from a workstation. I can see folders on the server but the Oracle tables on a D:\ Drive. I can only see them if I logon to the server or through Remote Desktop. The Server is \\ASams3 The Files are on D:\FamousDB\Database I can login as Admin on the server. Is there a special syntax or does the server need to be configured to allow this? I need to set up a DSN. Thank you for you help and God Bless, Mark A. Sam Links need to be established through oracle db engine running on server...

Text to Columns #4
I have a list of the Fortune 1000 companies in one cell. Is there a way to convert this information into columns with: Company City State Telephone 1 Wal-Mart Stores, Inc. Bentonville, AR 479-273-4000 2 Exxon Mobil Corporation Irving, TX 972-444-1000 3 General Motors Corporation Detroit, MI 313-556-5000 4 Ford Motor Company Dearborn, MI 800-392-3673 5 General Electric Company Fairfield, CT 203-373-2211 6 ChevronTexaco Corporation San Ramon, CA 925-842-1000 7 ConocoPhillips Houston, TX 281-293-1000 8 Citigroup Inc New York, NY 212-559-1000 9 International Business Machines Corporatio...

Unable to cut text when forwarding
I have a recently new problem, when I forward a message I am unable to cut and paste or in anyway alter the original message, I am using Outlook 2007 and it only started yesterday. Any ideas please ...

Text between Worksheets?
I've set up a budget workbook with 12 worksheets, one for each month. The first sheet contains the list of budget items. If I change the name of a budget item on the first worksheet, is there a way (or formula) to have this text automatically change on the other 11 worksheets? This would preclude my having to make the change individually on each of the other 11 sheets. How would I do this? Thanks! Select sheet2 then SHIFT + click on sheet12. In A1 enter =sheet1!A1 Drag/copy down as far as you need. The A1 will increment as you copy down. Select sheet1. Change A1 data and...

Free Form Text Box in Report
Hi All, I am running a report that runs from a Make Table Query, and then into a cross tab query. The report is in a Switchboard and runs off of a Macro that opens the make table, updates based off of the dates entered, closes it, opens the cross tab. Closes it. Opens the report in print preview. I need others to run the report with no intervention so I entered a text box (as I have done before) into the header that reads: =[Enter Report Ending Date mm/dd/yyy] to allow for the ending date to appear in the header. However, this is not working. I am recieving the following error &q...

Update query result to table
I can't find a good example on the web showing how to update table with the result from query. Table1 name "Agent1" column have: phone#(primary key), birthdate, and agent (total record 100). table2 name "Agent 2" have the same field (total record 150) except the data on agent is different. I run a query for this two tables and want to update the table1 with the table2 data where the phone#(primary Key). I want the result form the query will replace the agent in Agent1 table. How can i do that? UPDATE Table1 INNER JOIN Table2 ON Table1.[Phone#] = Table2.[Phone#]...