SQL Query for Excel

[otomatis]

Hi all,

I want to display excel data range from '2/1/2011 10:00:00 AM' to '2/4/2011 10:00:00 AM' using ADODB and VB6. The data result then display on the listbox but actually i get data that i don't want (date 10,22 are include).
Does somebody can help me..?

Here is code :

Dim oRS As ADODB.Recordset, nCols, Rcnt As Integer
    Set oRS = New ADODB.Recordset
    
   oRS.Open ("select * from [Trend Report$B18:B100] where Timestamp between '2/1/2011 10:00:00 AM' AND '2/4/2011 10:00:00 AM'"), oConn, adOpenStatic, adLockOptimistic
    nCols = oRS.Fields.Count        
    'Display the records
    Do While Not oRS.EOF
        sData = ""
        For i = 0 To nCols - 1
            sData = sData & oRS.Fields(i).Value & vbTab
            List1.AddItem (oRS.Fields(i).Value)
        Next
        Debug.Print sData
        oRS.MoveNext
    Loop
    
    'Close the recordset and the connection
    oRS.Close

Here is my data on excel(B18:B100) :
2/1/2011 10:00:00 AM
2/1/2011 11:00:00 AM
2/1/2011 12:00:00 PM
2/2/2011 10:00:00 AM
2/2/2011 11:00:00 AM
2/2/2011 12:00:00 PM
2/3/2011 10:00:00 AM
2/3/2011 11:00:00 AM
2/3/2011 12:00:00 PM
2/4/2011 10:00:00 AM
2/4/2011 11:00:00 AM
2/4/2011 12:00:00 PM
2/10/2011 10:00:00 AM
2/10/2011 11:00:00 AM
2/10/2011 12:00:00 PM
2/22/2011 10:00:00 AM
2/22/2011 11:00:00 AM
2/22/2011 12:00:00 PM

Result on the Listbox is:
2/1/2011 10:00:00 AM
2/1/2011 11:00:00 AM
2/1/2011 12:00:00 PM
2/2/2011 10:00:00 AM
2/2/2011 11:00:00 AM
2/2/2011 12:00:00 PM
2/3/2011 10:00:00 AM
2/3/2011 11:00:00 AM
2/3/2011 12:00:00 PM
2/10/2011 10:00:00 AM
2/10/2011 11:00:00 AM
2/10/2011 12:00:00 PM
2/22/2011 10:00:00 AM
2/22/2011 11:00:00 AM
2/22/2011 12:00:00 PM

Data i want to display on listbox :
2/1/2011 10:00:00 AM
2/1/2011 11:00:00 AM
2/1/2011 12:00:00 PM
2/2/2011 10:00:00 AM
2/2/2011 11:00:00 AM
2/2/2011 12:00:00 PM
2/3/2011 10:00:00 AM
2/3/2011 11:00:00 AM
2/3/2011 12:00:00 PM
2/4/2011 10:00:00 AM

[debasisdas]

you need to format the date.

when you say date between '2/1/2011' AND '2/4/2011' ---system considers this as from 2nd of jan till 2nd of april

and i think you want from 1st feb till 4th feb , right ?

[otomatis]

Yes, you right. That's all data are on february.

[debasisdas]

You need to format the data into -- MM/DD/YYYY format.

[otomatis]

You mean in my source data (excel) or in my query code..? If in source data i cannot do that, cause the data is automatically generate from another machine.

[debasisdas]

Sorry...

format the date not data.

[otomatis]

Hi debasisdas,

I have been changed the date format to mm:dd:yyyy hh:mm:ss AM/PM, but some data to be trim that has time 12:00:00 PM.

example once data is:
02/02/2011 12:00:00 PM
become
02/02/2011
the data appear without time.

Can you help me...?

[AndreRet]

I would rather write a sub to get the Date and then the Time seperate -

Dim xDate As Date
Dim xTime As Date
Dim xBoth As Date

xDate = Format(YourDateHere, "YYYY/MM/DD")
xTime = Format(YourTimeHere, "HH:MM:SS")

xBoth = xDate & " " & xTime

[debasisdas]

yes, the previous solution should work for you.

[AndreRet]

Thanks.:)