Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query returning more data than will fit on worksheet with VBA DB query...
...but the results will definitely fit. I am using a VBA to query
database and bring back results for three sheets in this workbook. Tw of them are fine but one intermittently gets this message: "The quer returned more data than will fit on a worksheet". If I click OK it the displays some of the results but not all (30 out of 45). This is eve stranger as initially it worked, then it stopped working. If I then tr constructing a new sheet it will work again. Anyone know why this i happening?! Woukd appreciate any advice. Here is the VB code I am using: Private Sub CommandButton1_Click() dDate = ActiveSheet.Range("D4").Value dDate = Format$(Cells(4, 4), "mmm dd yyyy") Set CurrentClaims = Worksheets("Current Claims").QueryTables(1) Sheets("Current Claims").Activate ActiveSheet.Range("A11").Select With CurrentClaims .CommandType = xlCmdSql .CommandText = _ "SELECT [DD Ref],[FE],[Worksource Ref] [DD Ref],[POL],[Ope Date] [Date Opened],DATEDIFF(D,[Open Date],'" & dDate & "') [Age o File],[Amount Claimed: Ins Client] [Amount Claimed],[Billed Costs [Costs to Date] FROM HRS_Array WHERE [Worksource Code]='IB AND [Clos Date] IS NULL AND DATEDIFF(D,'Sep 26 2003',[Open Date])=0 AN DATEDIFF(D,[Open Date],'" & dDate & "')=0 ORDER BY [Open Date]" .Refresh End With Set MonthTotals = Worksheets("Closed for Month").QueryTables(1) Sheets("Closed for Month").Activate ActiveSheet.Range("A11").Select With MonthTotals .CommandType = xlCmdSql .CommandText = _ "SELECT [DD Ref],[FE],[Worksource Ref] [DD Ref],[POL],[Ope Date] [Date Opened],[Close Date] [Date Closed],[Recovery Age (days) [Days taken],[Amount Claimed: Ins Client] [Amount Claimed],[Amoun Recovered: Ins Client] [Amount Recovered], CASE WHEN [% Recovered In Client] IS NULL THEN 0 ELSE [% Recovered Ins Client] END [ Recovered],[Billed Costs] FROM HRS_Array WHERE [Worksource Code]='IB AND [Open Date] 'Sep 25 2003' AND YEAR([Close Date])=YEAR('" & dDat & "') AND MONTH([Close Date])=MONTH('" & dDate & "')" .Refresh End With Set CumulativeTotals = Worksheets("Cumulativ Totals").QueryTables(1) Sheets("Cumulative Totals").Activate ActiveSheet.Range("A11").Select With CumulativeTotals .CommandType = xlCmdSql .CommandText = _ "SELECT [DD Ref],[FE],[Worksource Ref] [DD Ref],[POL],[Ope Date] [Date Opened],[Close Date] [Date Closed],[Recovery Age (days) [Days taken],[Amount Claimed: Ins Client] [Amount Claimed],[Amoun Recovered: Ins Client] [Amount Recovered],CASE WHEN [% Recovered In Client] IS NULL THEN 0 ELSE [% Recovered Ins Client] END [ Recovered],[Billed Costs] FROM HRS_Array WHERE [Worksource Code]='IB AND [Open Date] 'Sep 25 2003' AND DATEDIFF(D,[Close Date],'" & dDat & "')=0" .Refresh Sheets("Current Claims").Activate End With End Sub Thank -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Returning data from MS Query into worksheet - 2007 | Excel Discussion (Misc queries) | |||
another macro query - deleting a worksheet within a query | Excel Discussion (Misc queries) | |||
Returning data to Excel sheet from MS query | Excel Discussion (Misc queries) | |||
Excel 2000 Web query not returning all Table Data | Excel Programming |