Calling records from SQL and omitting blank lines

Question

Post reply

Calling records from SQL and omitting blank lines

jon pill

SSCommitted

Points: 1659
More actions
May 20, 2008 at 7:54 am

#190869

I have this code the pulls the data I want in a time frame from the current time to -1min back
However I have blanks rows, this is the way the database is.
How can I use excel to say leave bank cells out of the record set?
rsRecordset5.Open "select ESASKSIZE AS ESASKSIZE,ESBIDSIZE AS ESBIDSIZE from DBO.DEMANDSPREAD WITH (NOLOCK) WHERE TIMESTAMP BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND " & "DATEADD(ss, -1," & "'" & DSMAXTS & "'" & " )", gcnConnect
If Not rsRecordset5.EOF Then
Call Sheet4.Range("A2").CopyFromRecordset(rsRecordset5) 'ESASKSIZE
End If
rsRecordset5.Close
End Sub
Regards
Jon

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 1

It's not done by Excel... it's done by the query. Add this to it...

WHERE ESASKSIZE > ' '

AND ESBIDSIZE > ' '

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dragos_sv Default port Points: 1478 More actions · Answer 2

why don't you do it in sql?

and some limits, like

(ESASKSIZE is null and ESBIDSIZE is null)

or

(ESASKSIZE '' and ESBIDSIZE = '')

might work

dragos_sv Default port Points: 1478 More actions · Answer 3

forgot to say that records that meet those criteria should not be retrieved.

but i guess Jeff' s idea is faster

jon pill SSCommitted Points: 1659 More actions · Answer 4

rsRecordset5.Open "select ESASKSIZE AS ESASKSIZE,ESBIDSIZE AS ESBIDSIZE from DBO.DEMANDSPREAD WITH (NOLOCK) WHERE TIMESTAMP BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND " & "DATEADD(ss, -1," & "'" & DSMAXTS & "'" & " ) ", gcnConnect

If Not rsRecordset5.EOF Then

Call Sheet4.Range("A2").CopyFromRecordset(rsRecordset5) 'ESASKSIZE

'call Sheet4.Range("B2") = rsRecordset5.Fields(1).Value 'ESBIDSIZE

End If

rsRecordset5.Close

End Sub

Sorry I already have a where clause in there, where would I put a second where clause

Jon

jon pill SSCommitted Points: 1659 More actions · Answer 5

ok this is working for me thats alot for your replys

rsRecordset5.Open "select ESASKSIZE AS ESASKSIZE,ESBIDSIZE AS ESBIDSIZE from DBO.DEMANDSPREAD WITH (NOLOCK) WHERE ESASKSIZE > '' AND ESBIDSIZE > '' AND TIMESTAMP BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND " & "DATEADD(ss, -1," & "'" & DSMAXTS & "'" & " )", gcnConnect

If Not rsRecordset5.EOF Then

Call Sheet4.Range("A2").CopyFromRecordset(rsRecordset5) 'ESASKSIZE

End If

rsRecordset5.Close

End Sub

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

You bet... the columnname > '' keeps out Nulls and Blanks and still allows an index to be used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jon pill SSCommitted Points: 1659 More actions · Answer 7

There is one thing, I need the record setto come through sorted from oldest to newest, I have a time stamp or I could add a field that i can sort on

what would i need to do to tell the recordset to sort, currently the record set is not sorted as i would like

it appears to be jumbled up in areas

jon pill SSCommitted Points: 1659 More actions · Answer 8

So stupid its order by in sql, where i need to do it not in excel