MS Query, SQL Server 7 and Office 2001

I cannot get MS Query to work correctly when using the SQL Server 7
driver supplied by Microsoft. I am only having the problem under OS
8.6 - It is fine with OS 9.2.
I am using MS Query v9 which came as part of the Office 2001 Value
I am using the SQL Server 7 driver which came as part of the Data
Access Update for Office 2001.

The problem I am having is when I create a new query, I first select
my ODBC connection that uses the SQL Server 7 driver. Once selected, I
should be prompted for a username and pssword to connect to the SQL
database, but on OS 8.6, this username/password dialog box does not
display. The weird thing is though, that although the
username/password dialog box does not display I can still type the
username, press tab, type the password, press tab and then Enter and I
am take into the usual Query screen - so the dialog box is there, but
I just can't see it.

This is not a problem specific to just one machine - all of our
company's OS 8.6 machines have the same problem.

Can anyone out there help???
4/8/2004, 4:20:55 PM

0 replies

We have a Build Automation process that creates a database, installs all the tables, primary keys, column defaults, stored procedures, webpages, DAL, etc. Then, with a batch file, the Build Automation executes test cases on the SQL Server database. It does this, within the batch file, by calling: SQLCMD /Sserver /ddatabase /Q"RunTestCases" After the test cases are executed, I need to know if they all passed or not. I do this with a query. In the database, I have a table, Failures, with a list of failed test cases, if any. SQLCMD /Sserver /ddatabase /Q "EXIT(SELECT COUNT(*) FROM Failures) How do I capture the result from this query? I need to know if the result is 0 (zero) or not. What I do next in the batch file depends on the query result. If the result is 0, then do "this". If the result is > 0, then do "that". Thanks, Michael Bloomfield

Query Tables (excel odbc) memory problem
0 replies , 9/20/2005, 3:30:33 AM
I discover that when i recounting more then 200 QT on one sheet with this simply code Set qt_data = result_sheet.QueryTables.Add("ODBC;DSN=Excel Files;DBQ=" + ActiveWorkbook.Name + ";DefaultDir=" + ActiveWorkbook.Path + ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;", result_sheet.range("AA1"), sql_str) With qt_data .PreserveFormatting = True .FieldNames = False .BackgroundQuery = False .AdjustColumnWidth = False .RefreshStyle = xlOverwriteCells .Refresh End With qt_data.Delete my EXCEL.exe process (2003 ver.) becomes very slow, "hard" (~300-400 Mb) and "unstoppable" (closing frames, but not unload process) Can you give me any advice?

0 replies , 7/29/2007, 6:48:02 PM
I am searching on "Record Numbers" entered in a cell registered as the Parameter cell for a Microsoft Query link. I want to introduce a simple formula that will move it on to the the next or previous record in the sequence...

Query to Access : does'nt see tables
1 replies , 2/25/2004, 10:42:13 AM
Office 2000 SP3 FR From Excel / MS Query, I want to read data in Access tables. I choose the mdb file and so MS Query displays this error message (translated) : "This data source does'nt have any visible table". Original in French : "Cette source de donn�es ne contient aucune table visible." I transfert my data to a new mdb file : it work's few times (!), then the error message appears again. How to resolve this problem ? Jacques.

Vuillermet Jacques
mdx query
0 replies , 5/27/2005, 9:01:50 PM
I'm working with the sharepoint pivotchart web part. Is there a way to specify a mdx query rather than a cube name?

Querying Ranges
0 replies , 1/24/2006, 10:31:37 PM
I have a C# Automation Add-In that has a simple function like below. It purpose is to take a 2 strings and a range and query all the cells passed in the range. public string helloWorld(string s1,string,s2,object o3) { // Cast To Type Range Excel.Range passedRange=(Excel.Range) o3; // Loop Through Each Area Passed And Query Value2 Property foreach(Excel.Range rangeItem in passedRange.Areas) { string testValue=rangeItem.value2; } } for example lets say I want to pass 3 items in my range I would enter in my cell formula =helloWorld("string1","string2",($A$1,A$2,$A3) ) The code works fine but is really slow. Roughly a second or more on a single cell. I then copy the formula to a thousand cells it takes 6-8 seconds. I got this code from the MSDN site and can't find another way to query a range. Is there any way to query range with better performance or pass a collection values to a C# function?? Just as a footnote, the reason I am passing a range object is that my function needs to take a variable number of arguments and I don't seem to be able to use ParamArray in C# Automation class. If there is a better way of passing a variable number of arguments to function then please let me know. TIA Graham

Graham Lloyd
query analyzer runs query faster
1 replies , 12/12/2003, 8:57:01 PM
I've got a large database that I'm running queries against, one of which takes 35 seconds for pulling about 17,000 rows of data (from multiple, large joined tables). I ran the same query in Query Analyzer to understand the execution plan better and it runs in about 25 seconds. Multiple reruns (both in our application and in Query Analyzer) produce pretty consistent times, 35 and 25. Why would Query Analyzer be so much faster (and more importantly how do I match it). More info - the application is dynamically generating the query and is executing a variety of stored procedures before and after it (via ADO).

The Query Cannot be edited by the Query Wizard
0 replies , 12/2/2008, 2:51:00 PM
Good Afternoon I wondered if someone out there could help me solve a very annoying problem. We have two Windows 2003 Terminal Servers which have been setup as a load balanced cluster. We have Microsoft Office 2003 Professional installed on each one, it is up to date with all of the latest service packs and patches. OK now the problem is that we have an Excel spreadsheet which someone who has left the company has set up, it pulls data from one of our SQL servers into Excel. If I log into either of the Terminal Servers as the Administrator and try and edit the query attached to the spreadsheet then I get the Excel Query Wizard open and everything is fine. If I log on as myself (or anybody else for that matter) and bearing in mind that I also have administrative rights then I get the error message "The Query Cannot be edited by the Query Wizard" I click ok and then MS Query then opens up. Now the users that are going to be using the spreadsheet don't understand MS Query and want to use the Excel Query Wizard to do their work, I've tried as much as I know but to no avail, I believe the issue is a permissions problem within Excel it's self as the few users that need to access the data from the database on the SQL server already have the permissions granted. I have also tried to get one of the users to open up Excel locally (ie. outside of terminal services) and they do NOT receive this error message and get the MS Excel Query Wizard like they should. Any ideas gratefully received???

Connection Property for Query-based TableAdapter?
1 replies , 2/18/2007, 4:49:15 AM
I've just discovered something really annoying: if you drag and drop a stored procedure onto DataSet design surface in VS2005 you get what >>looks<< like a TableAdapter...only it isn't. Specifically, it doesn't expose a Connection property. The same thing happens if you create a DataTable object on the DataSet design surface and have it initialize itself from a stored procedure (rather than from SQL statements). No Connection property! This is really, really annoying, when it comes time to deploy a website application to a production server: the query-derived TableAdapters still "point" at the development database. What's worse, the problem doesn't surface until you go to a page on the production server that uses on of these query-based adapters...and then the page blows up because it can't access the development database server. So, several questions: 1) What's the workaround? The only one I've been able to come up with is to make separate production and development DataSets in the VS2005 project. I guess I could wrap a class around them which returned the "right" one for the active environment. But that sounds ugly and flaky. 2) Why is there a distinction between TableAdapters derived from stored procs and TableAdapters based on SQL statements? 3) If the answer to #2 is "it's a bug", why wasn't it fixed in VS2005 SP1? (It wasn't; I'm running SP1) 4) When will the fix be made available? - Mark

Mark Olbert
3 replies , 11/8/2006, 3:32:11 PM
Howe do I start running a macro?

Date query
4 replies , 10/22/2007, 8:46:03 AM
Hi, What is the simplest way to check 5 different dates to see if they lie within 4 seperate date ranges(Yearly quarters) and count the number of days attributable to each quarter? eg Q1 Jan 1 - Mar 31 Q2 Apr 1 - Jun 30 Q3 Jul 1 - Sep 30 Q4 Oct 1 - Dec 31 Voyage: 24 Mar, 25 Mar, 4 Apr, 5 Apr & 14 Apr This voyage to place 7 days in Q1 (24 Mar to 31 Mar) & 14 Days in Q2 (1 Apr to 14 Apr) I need to check around 200 voyages at a time. Is there an easy way? -- Many Thanks, CK

3 replies , 1/3/2008, 6:36:01 PM
Is there a way to create a query which can then be run by another user on a different pc? -- TW

Queries being used by other queries
1 replies , 8/28/2008, 2:44:02 PM
I want to modify an existing query in my database. I just can't remember if I'm using it for something else as well. How can I find out if a query is being accessed by another query or queries in a database?

email access query as excel attachment
1 replies , 9/22/2006, 7:37:02 PM
I'm trying to automate email some of our monthly reports. So far i've made a connection to an Access database and have been able to run some SQL statements and loop throught recordsets to test the connection. What I'm not sure how to do is to put the results of a query in excel and attach it to an email. -- Billy Rogers Dallas,TX Currently Using Office 2000 and Office 2003

Formula Query
2 replies , 10/29/2007, 9:40:01 AM
I am using a formula to count the number of occurances of an entry - in an ever expanding list of data. The formula that I am using is as follows: =COUNTIF(ActualWeight,">=20")-COUNTIF(ActualWeight,">29") This counts the number of occurances of a weight between 20 and 29 (where ActualWeight is a named range for the data). This formula works absolutely fine and is bringing back a count. I would now like to extend the formula so that it only counts the above if it meets another criteria (a school name) - which has a named range of SchoolName. Does anyone have any suggestions on what I could try - I have attempted a couple of things and I cannot seem to get it to work. Thanks in advance for your help.

Formula query
2 replies , 5/12/2005, 8:40:02 AM
How do I create a formula where the value of one cell is dependent on the value of two other cells? This formula would form part a nested if function.

Pivot: Selecting data with Query Result
1 replies , 11/23/2005, 6:45:05 PM
Hello Community, I am using Pivot functunality to get access to data that is stored in a classical table. As I result from selecting the name in the the page field, I would to display the corresponding values like customer number and post code just below the name field but not in the data field. Moreover, I want to be able to enter the customer number once and another time the name, whereas each time the "related field" is filled correspondingly. Could anybody help me on that issue. Thanks and cheers. Glenn Format of the source data: #Name #Customer Nr. # Postcode # Revenue 2000 # Revenue 2001 ....

A Query
6 replies , 11/1/2007, 9:12:56 AM
Hi, I have been a user who have always done simple tasks like creating table, formatting etc in Excel. But time requires that I need to get used to using formula and other tools of excel therefore my query might be so simple for the experienced users but I don't know how it can be done. So excuse me for my ignorance: I have a table that contains dates in one cell and time stamp in the subequent cell and the same formate spanning over three months. I need to exclude those cell having time stamp and count (month-wise) those cells having date in them. For example: COLUMN A 31-Oct-07 10:49:27 PM 31-Oct-07 12:18:28 PM 30-Oct-07 12:32:27 PM Can anyone help me? Thanks, Pankaj

Pankaj Trivedi
1 replies , 9/10/2003, 4:17:14 PM
I want to use MS Query. The setup program seems to indicate that MS Query is installed yet I see no evidence of it on my hard drive.

Very Strange MSFT Word Query
1 replies , 5/28/2009, 9:50:01 PM
I have an official copy of Office 2007 installed on my Mac. When I installed Office I registered it with my own name details. I recently created and saved a document on my laptop using Word. In the "get info" for this document it shows the author as someone else completely different, someone known to me who definitely never had access to my computer or my document. This is an important issue for me (I'm now being accused of plagiarism) and I'm completely baffled at to how this could of happened. Any helpful comments would be hugely appreciated.

Auto filter Query??
7 replies , 3/3/2007, 3:51:46 PM
Hi hope this is possible. I would like to run a macro that uses a custom filter to do the following a Date is enetered into j18 (start date) and a date entered into k18 (end date) is it possible to use auto filter to run a custom macro where as the criterias are >= j18 and < k18 How would i accomplish this, any help with some code would be useful i tried the following without much success Sub daterange() Selection.AutoFilter Field:=2, Criteria1:=">=Cell(j18)", Operator:= _ xlAnd, Criteria2:="<Cell(j18)" End Sub HYCH Steve

1 replies , 8/9/2004, 9:32:32 AM
Hi, I have two excel 97 files, the record is over a thousand File A Invoice no. Amt 4001 2 4002 3 4003 4 4012 3 4015 5 File B Invoice no. Pamt Pdate 4001 2 4-7-04 4012 3 13-7-04 I want to create a file to as follow Invoice no. Amt Pamt Pdate How to make it under Excel 97 ?? I already tried to use Query or Pivot Table If I use Access, I use Query to do it and print it but I do not have a licence in the office. Please help Jimmy

Excel query help with union select
2 replies , 11/22/2009, 7:18:28 AM
First of all i want to thank the people here who have been answering my posts. I am learning alot as a beginner. This forum is very active and has some talented people. Now to the question, I am trying to get a union select to work on a past problem i worked on. The problem is that when i copy and paste in my code i dont seem to understand how to seamlessly paste in code without an error. Can someone tell me what i am doing wrong? There is something wrong with Defect$. Here is what i have, below, but near the top there is an error but i cant see it? while in ms excel using query i get the error,DEFECT$ is not a valid name. Make sure that it does not include invalid characters or punctuation and that is is not too long SELECT `Employees$`.ID, `Employees$`.Employee_Last_Name, `Employees $`.Employee_First_Name, `Employees$`.`Starting Week` FROM `C:\Users\Andrew\Downloads\11+Employee+Start +Dates.xlsx`.`Employees$` `Employees$` UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F4, `Production$`.F4, `DefectRates$`.F4 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F5, `Production$`.F5, `DefectRates$`.F5 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F6, `Production$`.F6, `DefectRates$`.F6 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F7, `Production$`.F7, `DefectRates$`.F7 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F8, `Production$`.F8, `DefectRates$`.F8 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F9, `Production$`.F9, `DefectRates$`.F9 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F10, `Production$`.F10, `DefectRates$`.F10 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F11, `Production$`.F11, `DefectRates$`.F11 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F12, `Production$`.F12, `DefectRates$`.F12 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F13, `Production$`.F13, `DefectRates$`.F13 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F14, `Production$`.F14, `DefectRates$`.F14 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F15, `Production$`.F15, `DefectRates$`.F15 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F16, `Production$`.F16, `DefectRates$`.F16 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F17, `Production$`.F17, `DefectRates$`.F17 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F18, `Production$`.F18, `DefectRates$`.F18 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F19, `Production$`.F19, `DefectRates$`.F19 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F20, `Production$`.F20, `DefectRates$`.F20 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F21, `Production$`.F21, `DefectRates$`.F21 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F22, `Production$`.F22, `DefectRates$`.F22 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F23, `Production$`.F23, `DefectRates$`.F23 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F24, `Production$`.F24, `DefectRates$`.F24 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F25, `Production$`.F25, `DefectRates$`.F25 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F26, `Production$`.F26, `DefectRates$`.F26 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F27, `Production$`.F27, `DefectRates$`.F27 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F28, `Production$`.F28, `DefectRates$`.F28 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID UNION ALL SELECT `Defects$`.ID, `Defects$`.Last_Name, `Defects$`.First_Name, `Defects$`.F29, `Production$`.F29, `DefectRates$`.F29 FROM `Defects$` `Defects$`, `Production$` `Production$`, `DefectRates $` `DefectRates$` WHERE `Defects$`.ID = `Production$`.ID AND `DefectRates$`.ID = `Production$`.ID

Formula query
8 replies , 11/19/2004, 4:45:29 PM
Hi I've devised a basic spreadsheet which calculates the cost to hire a unit. Col A = Item No Col B = Date from Col C = Date to Col D = No of days Col E = Cost per day Col F = Net cost Col G = VAT Col H = Gross This works fine, but what I want to include is the minimum rental period is three months. So If someone rents the item for less than three months, the figure in the net cost column would show the minimum cost TIA Steve

Query Options Causes Crash
0 replies , 2/16/2004, 7:43:58 PM
Every time I click on Query Options when using the Office Address Book as a Data Source in Data Merge Manager Word crashes after between 20 � 30 seconds. I have tried all the usual fixes like deleting and re-creating, deleting and re-creating preferences, uninstall office vX and re-install and repairing file permissions. Is there anything else I can try? Thanks in advance Nick

Nicholas Johnson
Turn Off Auto Query on saved query
2 replies , 9/29/2004, 2:53:04 PM
I saved a query in MS-Query and now want to change it, but I cannot un-press the Auto Query button to allow me to edit it. How is that done?

Query analyzer bug ?
0 replies , 8/25/2005, 3:02:10 PM
Hi, I am brandnew to SQL2000 and I am learning. My problem is the Query analyzer. Everytime when I run predefined function I got an error. Eg. On a certain database I use of the Security Functions "IS_MEMBER" (or whatever function) and the error = "Server Msg 170, Level 15, STate 1, Line 1 : incorrect syntax near '<'. The syntax = "SELECT IS_MEMBER(<group_or_role, sysname, >) Again: every function gives this error. How can I correct this ? regards, Ger. *** Sent via Developersdex ***

Ger Eielts
Hi. I am using Microsoft Excel 2002 SP-2. Under Data\Import External Data, the \Edit Query option is 'grayed out'. The \New Database Query option is not. I have MsQueries that I created when on an earlier version of the Office suite, but I can't get to them directly now. I'm assuming the \Edit Query option would let me. What do I need to do to activate this option? Thanks for you help. Steve

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a sheet that has 23 external database queries (via ODBC to a MySQL database), each of which generally is on its own tab. Data from these queries is rolled up into a formatted weekly summary sheet for presentation purposes. The issue is that when some of the queries are edited in MS Query, the changes are reflected in the returned data, but the new version of the query is not saved. When you "edit query" again, the old one is there, and needless to say, the next time you perform a "refresh data" it's returned in the older format. However, some of the queries can be edited and saved properly. I can't figure out a pattern as to which queries save and which don't, other than that it seems like the ones I created more recently don't save. However, I can't guarantee that. For the tables that don't save, "save query definition" under "data range properties" IS checked. My kludgy workaround is to have the current SQL next to the list on each of the problem tabs, and copy and paste it via "edit query" into each problem query before running them every week. However, that's a big pain and I'm hoping there's a real solution out there.

Microsoft Query not functional
0 replies , 9/9/2003, 7:27:15 PM
When I am trying to import data with a new database query (Data | Import External Data | New Database Query...) Excel goes and calls MSQuery, and I see the program icon in my start bar, but MSQuery never actually ends up showing up on the screen. I click on the program, with no response, right clicking does nothing. Minimizing / sizing excel child windows does not reveal the app. (I've used MSQuery in older versions of Excel) When I attempt to open MSQuery again from the same menu item, I get a message that states "Microsoft Query is busy editing another query for Microsoft Excel. You can not edit more than one query at a time from within Microsoft Excel. Switch back to Microsoft Query to complete (or cancel) the editing operation currently in progress." I have installed all the current patches for Microsoft Office XP and have even 'repaired' the installation on my Windows XP Pro system, to no avail. I CAN get Microsoft Query to work if I launch the application directly, but then the results are not linked to Excel. Any suggestions on how to get Microsoft Query to work properly?

Ron McMahon
Microsoft Query: Parameter Query?
2 replies , 4/1/2009, 10:18:01 AM
Hi, In Excel I have a connection to a database. This connection uses a Query. In this query I want to add parameters that will change. For instance, I want the query to return only rows of data between a 'start' and a 'finish' date. How can I accomplish that? Microsoft Help talks over "parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.)." I could not find where or how I can accomplish that. Abel.

Hello, all, I am working on preparing for my MOUS Expert Certification on Excel, and was wondering if anyone knew of any no-cost online sources for information/practice tests/training, etc. Specifically, I have yet to find an intelligible description of what is meant in the study outline by 'HTML Round Tripping'. I imagine it may be as simple as just saving as an HTML format file, then retrieving back into Excel with data intact, but have yet to find any books or online sites with a definitive take on exactly what is meant by the term. Also, if I want to query on an external Excel data file, with column labels, row labels, and a simple 2D matrix of data on the data source worksheet, what do I need to do in the original Excel worksheet so that Query will recognize the table in the worksheet? Thanks for any help, Richard

Richard Adams
If statement query
3 replies , 4/20/2007, 8:38:06 AM
In a column i have some values starting with "TBA, AAA, BBB 123" . If the string begins with TBA, then i want to return TBA in another cell. else i want to return the value "Existing". Is there a function i can use to do this?

problem with long queries in MS Query
1 replies , 4/15/2004, 11:07:58 AM
I have a spreadsheet that I developed on a PC using the Windows version of Query. When I open it and do edit query, it says that the syntax is bad and everything below a certain point in the query is garbage characters. I open the original query in BBEdit, copy it over to Query, and hit test. It returns the data fine, and it also populates the spreadsheet fine when I tell it to return the data. But if I immediately go to edit query again, I get the same garbage characters, starting at the same point in the query as when I originally opened it, plus all the line breaks are gone. If I rearrange some of the joins, the query still works when I first copy it over from BBEdit, but when I go back to edit the query, it has garbage at the bottom again, only this time it starts in a different place. So it appears that there is some sort of length limit on queries (the query I'm working on is fairly long with a large number of joins and where statements). I have tried opening my raw query with multiple text editors, removing line breaks, etc., and I come up with the same results every time. It looks like the problem happens when Query itself saves the query and does some reformatting. Has anyone else encountered this? Is there a workaround?