Re: Query not returning all the rows

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

As I said, these changes won't fix anything, but still worth doing.
Long is better as you never know what might happen to the code in the
future and it will be (though unmeasurable) faster. Using & instead of +
will make the code clearer.
Is the Selected range fine at the Copy?

I don't 2007 yet, so not sure what could be causing the trouble.

RBS

"barnabel" <barnabel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:374CC579-29EC-41CD-AF4B-ECF1115879D0@xxxxxxxxxxxxxxxx
Period is indexing Columns so it really doesn't need to be a long. Each
column on the parameter page contains the information to specify a range of
data. The sheet works perfectly for up to 1 years worth of data whether I
specify it as jan-dec in 1 column or jan-mar, apr-jun,jul-sep and oct-dec but
somebody asked for 3 years worth of data. Even so period is less than 10.

What difference does using & rather than + do? The resulting query printed
and copied to the sql window works fine.

"RB Smissaert" wrote:

Can't see yet where it might go wrong.
It won't fix it but I would declare Period as Long and use the & to
concatenate
strings rather than the + you are using.

What is happening here:

End With

There were 2 lines that were irrelivant so I didn't include them but I
missed the line:

Cells.Select

so if selection is not right then it is still an excel problem that it isn't
selecting the right range when there are more than 65K rows. I'll look at
that.
Selection.Copy

Workbooks.Add

Is Selection the right range here?


RBS



"barnabel" <barnabel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1228CDA3-6D75-43FF-B3E1-790BC741FB8A@xxxxxxxxxxxxxxxx
> Here is the code. The actual query should be irrelivent. If I print > the
> sqlstring in the immediate window and paste it into an SQL command > window
> it
> returns all the rows.
>
> Dim sqlString As String
> Dim period As Integer
> Dim client As String
> Dim sourceBook As String
> Dim targetBook As String
>
> sqlString = ""
> period = 1
> sourceBook = ActiveWorkbook.Name
>
>
> While Not IsEmpty(Sheets("Parameters").Cells(2, period + 1))
> If (sqlString <> "") Then
> sqlString = sqlString + "union all" + Chr(13)
> End If
> sqlString = sqlString + buildQuery(Sheets("Parameters").Cells(2,
> period + 1), "pba", Sheets("Parameters").Cells(3, period + 1),
> Sheets("Parameters").Cells(4, period + 1), _
> Sheets("Parameters").Cells(5, period + 1),
> Sheets("Parameters").Cells(6, period + 1), > Sheets("Parameters").Cells(7,
> 2))
> sqlString = sqlString + "union all" + Chr(13)
> sqlString = sqlString + buildQuery(Sheets("Parameters").Cells(2,
> period + 1), "buy", Sheets("Parameters").Cells(3, period + 1),
> Sheets("Parameters").Cells(4, period + 1), _
> Sheets("Parameters").Cells(5, period + 1),
> Sheets("Parameters").Cells(6, period + 1), > Sheets("Parameters").Cells(7,
> 2))
> period = period + 1
> Wend
>
> Sheets.Add
> ActiveSheet.Name = "rawData"
> Sheets("rawData").Cells(1, 1).Select
> With Sheets("rawData").QueryTables.Add(Connection:= _
>
> "ODBC;DSN=Sybase;SRVR=server;DB=database;UID=user;PWD=password", _
> Destination:=Range(Sheets("rawData").Cells(1, 1),
> Sheets("rawData").Cells(1, 1)))
> .CommandText = sqlString
> .Name = "RawCompetativeData"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = True
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .PreserveColumnInfo = True
> .Refresh BackgroundQuery:=False
> End With
>
> Selection.Copy
>
> Workbooks.Add
> targetBook = ActiveWorkbook.Name
> Workbooks(targetBook).Activate
> ActiveSheet.Name = "rawPerformance"
> Sheets("rawPerformance").Select
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
> Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
> SkipBlanks:=False, Transpose:=False
> Workbooks(sourceBook).Sheets("Parameters").Copy
> After:=Workbooks(targetBook).Sheets("rawStationPerformance")
>
>
> "RB Smissaert" wrote:
>
>> Could you post the relevant code?
>> Did you use an Integer variable somewhere?
>>
>> RBS
>>
>>
>> "barnabel" <barnabel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:EC022750-CB61-47C9-ADE8-EA13F1C1637A@xxxxxxxxxxxxxxxx
>> >I have Excel 2007 which is supposed to support sheets longer than 65K
>> >rows.
>> >
>> > I have a query that should return more than 135K rows. But there >> > are
>> > only
>> > 65K rows in the sheet. Anybody know how to set the maximum number >> > of
>> > rows
>> > returns?
>>
>>



.

Relevant Pages

  • Boolean value stored in a combobox!
    ... I use the stored values in tblStorage to set some kind of default value next ... cboMemberCard instead of an empty string. ... selection. ... Dim db As Database, rst As Recordset ...
    (microsoft.public.access.formscoding)
  • Re: Query using 3 listboxes
    ... Dim MyDB As DAO.Database ... Dim strWhere As String ... Dim varItem As Variant ... MsgBox "Please make a selection from each list",, "Selection ...
    (microsoft.public.access.formscoding)
  • Re: Boolean value stored in a combobox!
    ... > I use the stored values in tblStorage to set some kind of default value ... > cboMemberCard instead of an empty string. ... > selection. ... > Dim db As Database, ...
    (microsoft.public.access.formscoding)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strWhere As String ... Case Else 'Multiple Selection ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query not returning all the rows
    ... Is Selection the right range here? ... Dim sqlString As String ...
    (microsoft.public.excel.programming)