Post reply

Calling records from SQL and omitting blank lines

  • 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

  • May 20, 2008 at 8:05 am

    #817912

    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)
    Intro to Tally Tables and Functions

  • May 20, 2008 at 8:06 am

    #817915

    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

  • May 20, 2008 at 8:09 am

    #817921

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

    but i guess Jeff' s idea is faster

  • May 20, 2008 at 8:12 am

    #817928

    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

  • May 20, 2008 at 8:15 am

    #817934

    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

  • May 20, 2008 at 10:24 pm

    #818347

    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)
    Intro to Tally Tables and Functions

  • May 21, 2008 at 8:36 am

    #818607

    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

  • May 21, 2008 at 8:57 am

    #818628

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

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

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