Search results for query: *

Thanks John. What a pain! I saw the ActiveSheet part, but I just figured that Excel didn't care that it wasn't plural (kind of like Sheets(1).Select). I am using Excel 2003 SP2 and I am still having that problem. Maybe Excel 2007 has it fixed? Using a loop to do the page setup at the end...

I tried that. I used:.Sheets(Array("130983", "210232", "222574", "240213", "240219", "240223", "240376", _ "240462", "240463", "240464", "240465", "250004", "360003", "360009", "480004", _ "500250", "500637", "520255", "520268", "550222", "550225")).Select .Sheets("130983").Activate...

I am using an Access macro to export data to multiple Excel worksheets. I have all of the data going to Excel and most of the formatting working. Now I need to set the Page Setup options for all of these worksheets. I am zooming to 90%, adding a footer, changing margins, and repeating rows at...

Perfect, thanks!

I am trying to export a query to multiple worksheets in Excel. I am using TransferSpreadsheet and just exporting the query works. When I try some Excel formatting within the macro, I eventually am prompted to save a copy of the file. I saw some other posts, which suggested it was a memory...

I want to get the 4/1 entry. First I want to get the earliest paid date (that meets the criteria), then if they are equal, the earliest due date. Then once the earliest have been determined, the PrevBalance calculation from that record. I have now found some rare occurances where there are...

I'm sorry for being vague, but I don't want to get into privacy issues by posting too much information. Here is the SQL: SELECT db_history.loan_id, Min(db_history.paid_dt) AS MinOfpaid_dt, Min(db_history.due_dt) AS MinOfdue_dt, [prin_amt]+[prin_bal_amt] AS PreviousBal FROM db_history WHERE...

Unfortunately, that will not work as it sums all of those two fields when they meet the criteria. Essentially, I need to find the record that meets the requirements and then has the minimum paid_dt (and if multiple with that paid_dt, the minimum due_dt, which should find a unique record). I...

I am trying to pull the minimum of two fields and then add up two other fields from the minimum record in the table. Here is the SQL: SELECT db_history.loan_id, Min(db_history.due_dt) AS MinOfdue_dt, Min(db_history.paid_dt) AS MinOfpaid_dt, [prin_amt]+[prin_bal_amt] AS PreviousBal FROM...

That works great. Inside the query, I am using:Max(Val(Replace(field, ",", ""))) It seems much faster than using the loop inside of a function (obviously). Thanks for the help!

Okay, here's what I was able to come up with using the Val function: Public Function ToNumber(inputString As String) As Long If inputString Like "*[A-Z]*" Or inputString Like "*[a-z]*" Then ToNumber = 0 Else Do While InStr(inputString, ",") > 0 inputString =...

Unfortunately, some of the values are entered with commas. Val(3,000) gives me 3. I suppose I could go through and eliminate the commas using an InStr loop and then Val the results... I'll give that one a go. Thanks!

Here's one version:SELECT dbo_coverage.loan_id, IIf([coverage_requirement] Is Null,0,IIf([coverage_requirement] Like "*[A-Z]*",0,CLng([coverage_requirement]))) AS [Number] FROM dbo_coverage INNER JOIN dbo_accounts ON (dbo_coverage.loan_id = dbo_accounts.loan_id)ORDER BY dbo_coverage.loan_id...

I have a string type field that contains text, numbers and NULLs. I am setting all the Null or strings (like *[a-z]*) to zero and CLnging the others. Then I need to get the max of the new values (or just sort them descending). Whenever I add the sort or max, I get a "Data Type Mismatch"...

I was going to mention that I didn't think they were in the help file (I searched for constants and xlCount). I never knew about the object browser. Very interesting. Thanks!

Thanks!! That worked (of course). I believe you have helped me on more than one occasion. I really appreciate it. Is there a place where all of these Excel constants are listed? Thanks again!

Hello, Thanks to this forum, I've learned a lot about controlling Excel documents from within an Access macro. Most of the time, I can record the Excel macro and with a little tweaking (put a . in front of the line) get it to work in Access. I am now converting an old Excel macro and I'm not...

It was a query that pulled data only when the data was refreshed via the external data menu. I was able to find a solution to the problem. A colleague was able to extract the query from the worksheet (I missed the process). Then I recreated the sheet using the SQL statement and everything...

Thanks for the quick reply. How can I right-click on the query? I don't think there is an external file anywhere (I've made a copy of the offending Excel file to my system and I have the same problem). I am using the Import External Data menu to try and edit the query, but I can't find...

I recently got a call to help someone with an Excel problem they were having and I can't figure it out. It is an old Excel file I believe is pulling data from an external database (ODBC) using MS Query. When I try to edit the query, it says "waiting for data to be returned by Microsoft Query"...