Query returning more data than will fit on worksheet with VBA DB query...

C

ChrisSmith

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