Recent content by ebrooks54

Tables, Views, System Tables, and Synonyms are all Checked. If the file isn't open, the Named Range is not appearing.

Thanks for replying. The range does not appear to be calculated. According to Name Manager it is defined as =OTIS!$A:$BX. OTIS is the name of the worksheet. In playing with MSQry32, I have noticed that if the spreadsheet is not open, the available tables it discovers are OTIS$ and...

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...

Skip, Thanks very much for the feedback. I am looking at re-recording the macro, but I am not immediately seeing how to stop the macro from doing more than I want. XL2007 seems intent on inserting headings, activating filters, and applying a table-style. All I want it to do is retreive the...

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...

Thanks, Randy. That did not seem to help: "Operation must use an updatable query" was the result.

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...

Skip, My apologies. What you suggested did work; brilliantly. I had a typo somewhere in the convoluted statement, which now reads: =INDEX(OFFSET(INDIRECT(G7),0,4,ROWS(INDIRECT(G7)),1),MATCH(F7,OFFSET(INDIRECT(G7),0,2,ROWS(INDIRECT(G7)),1),-1)) Many, many thanks for your help.

Skip, Maybe I have expressed myself poorly, but this fails in two ways. The replacement for the named range "A1A5E8" in my formula has to reference the value in G7. If I just make the change as you suggest, the result is #Value!. In terms of the construct I am creating, there are a series of...

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...

This is brilliantly simple to suppress the printing of a zero value. I'm not sure that the 'can shrink' parameter is working, though. It appears that the space that the field would take is still being reserved. That's a minor issue, I can live with.

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...