Search results for query: *

I have an XL2003 spreadsheet that a user has created an MSQuery in to a named range in another XL2003 spreadsheet. In XL2007 it appears this only works if the spreadsheet being queried is opened first. If it is not open, the spreadsheet doing the querying gets an error message saying it can...

I have a macro (see code below) in an XL2003 spreadsheet that works fine until it is run in XL2007. It fails on the .Refresh statement with a run-time error 1004, ODBC failure. I recorded a macro to try and recreate the code in XL2007, and noted that instead of using QueryTable.Add, XL2007...

Maybe I am having a brain spasm. In any event, I don't know what I am doing wrong... Using Access 2007, I am trying to create an update query that will update an Access table with data contained in a linked .csv file. Anything I try gives me an error message saying that updating data in a...

This may be about the most complicated formula that I have come up with as an amateur, but I am afraid it needs to be a little bit more complicated abd I can't figure out these last two steps: =INDEX(OFFSET(A1A5E8,0,4,44,1),MATCH(F7,OFFSET(A1A5E8,0,2,44,1),-1)) A1A5E8 (which is a Canadian...

I haven't noticed this behaviour before, and it really has me scratching my head... Using Access 2003 I have a table with 12,104 records in it. A query with no criteria returns 12,104 rows. A query with Message Type = 'TMSG' returns 1,312 rows. A query with Message Type <> 'TMSG' returns...

Is there a way in Access to only print a value in a report if it contains something meaningful (IE non-zero)? I have a record with multiple tax jurisdictions in it, and I only want to print the description and the value, if the value is non-zero. If the value is zero, it should print nothing...

I thought I had a creative answer to a problem I was having, but it doesn't quite work: I have a spreadsheet that retrieves external data from an Access Table. In the spreadsheet, I have a named range called "AsofDate", which I link to in the Access Database. In the query in Access, there is...

An annoying feature about Excel: If you launch a spreadsheet from an email attachment or from an explorer window, and Excel is not already open, you do not get macros that are stored in personal.xls or in any custom .xla's. Apparently, they are only available if you open the application before...

I have a macro in Access 2003 that has the following condition associated with it: Dir("Indigo POS Data.TXT")>"" In Access XP the query ran with no problems. Running it under Access 2003, I am getting a dialog box with the message: "The expression you entered has a function name that ...

I'm sure this must be in the forum somewhere, but I can't find it... I have a machine with WinXP Pro, and a second drive mounted. On the machine there are two users, UserA and UserB. The highest level folder on the second drive is called F:\main and it is shared. I'm not sure how I ended up...

I'm stumped on this one. I have an Excel spreadsheet which is to be used to generate a catalog. There is a column named 'Width' that contains a number, ie 6.125. The author of the spreadsheet (not me) formatted the column so that it displays as '6 1/8'. This is the way the enduser would also...

I'm looking for a way to create something that duplicates the way the sumif function works in Excel, except that it works the way the subtotal function works and ignores rows that are hidden because of an auto-filter. Alternately, is there a function that indicates whether a row is hidden or...

I have a document that has 2 Headers in it that Word Identifies as "First Page Header", and "Header". The FPH is the same as the Header, except that it contains a graphic. The document is 7 pages long. Immediately after loading the document, I activate the macro recorder and do the following...

Short and Sweet: How do you deploy a spreadsheet that has queries in it? Longer Explanation: I figured out how to automate the process of pulling data out of a text file downloaded from another computer, I created an MS:Text Driver in ODBC, defining fields and a special delimiter "|", and...

I apologise, if this isn't quite the right place to post this question, but it is related to an Access Query. I have a query in an Access DataBase called "Test", and I want to load it in an Excel spreadsheet. In Excel, there appear to be 2 ways to do this: 1) Data/ Import External/Import Data...

Nobody ever said my job would be simple. I have a tab delimited text file that needs to be imported and processed. Unfortunately the file, as received has formatted numbers with commas separating the thousands: 5,916 for example. A straightforward import fails as it won't recognize as a valid...

First, many thanks to the people in this forum that take the time to respond to questions. This is a great place to expand one's working knowledge of Access. Second, an observation: If the structure of a table says that a particular field has a default value of 0, I would have expected that...

Maybe I'm just not seeing clearly, but it seems like it should be simple enough to do: Two tables: History Table: ProdCode / Description / TotSales Transact Table: ProdCode / QtySold I want to do an update query that increments TotSales in the History Table, or if the ProdCode does not exist...

I just can't visualize how to do this one. Any help would be appreciated. Table looks like this: Acct / Rep ID 001 / A1 001 / A2 002 / A1 002 / A2 002 / A3 Output Table needs to be this: Acct / Rep1 / Rep2 / Rep 3 001 / A1 / A2 002 / A1 / A2 / A3 Thanks

Is there any documentation, white papers, guidelines, or discussions on code optimization? I've been working hard to develop a series of Excel and Word Macros to generate an order form. In the almost final step, I have created a Word macro that steps through rows of a table that were populated...