Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. NMiller007

    Page Setup for All Worksheets

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

    Page Setup for All Worksheets

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

    Page Setup for All Worksheets

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

    TransferSpreadsheet to Multiple Worksheets

    Perfect, thanks!
  5. NMiller007

    TransferSpreadsheet to Multiple Worksheets

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

    Mins Causing Not Part of Aggregate Function Error

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

    Mins Causing Not Part of Aggregate Function Error

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

    Mins Causing Not Part of Aggregate Function Error

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

    Mins Causing Not Part of Aggregate Function Error

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

    Sort or Max on CLng

    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!
  11. NMiller007

    Sort or Max on CLng

    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 =...
  12. NMiller007

    Sort or Max on CLng

    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!
  13. NMiller007

    Sort or Max on CLng

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

    Sort or Max on CLng

    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"...
  15. NMiller007

    Excel Selection.Subtotal in Access Macro

    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!
  16. NMiller007

    Excel Selection.Subtotal in Access Macro

    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!
  17. NMiller007

    Excel Selection.Subtotal in Access Macro

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

    Can't Refresh Data or Edit Query of Excel File

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

    Can't Refresh Data or Edit Query of Excel File

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

    Can't Refresh Data or Edit Query of Excel File

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

Part and Inventory Search

Back
Top