[d_rop4nme]

how does one go about getting the upper and lower bounds of dates in an array

I know there is a way to do this can't remember everything I Google is for numeric values ... ?

[raziel_]

what do you mean get the upper and lower bound of an array of dates? like this:

Private Sub Command1_Click()
    Dim arrDate() As Date   'an array of dates'
    ReDim arrDate(20)       'resize the array with 21 elements'
    MsgBox UBound(arrDate)  'the top most element of the array'
End Sub

[d_rop4nme]

Yeah I tried something like that toO test:

        'On Error Resume Next
        Const adOpenStatic = 3
        Const adLockOptimistic = 3
        Const adCmdText = &H1

        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordSet = CreateObject("ADODB.Recordset")
        
        strPathtoTextFile = "C:\Documents and Settings\dwetherell\Desktop\"
        
        objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & strPathtoTextFile & ";" & _
                  "Extended Properties=""text;HDR=YES;FMT=Delimited"""
        
        objRecordSet.Open "SELECT * FROM sumDetailData.csv", _
                  objConnection, adOpenStatic, adLockOptimistic, adCmdText

        Dim DOS_Arr() As Date, DOE_Arr() As Date, MatNo() As String
        
        Dim i As Integer
        i = 0
            Do Until objRecordSet.EOF
            ReDim DOS_Arr(i) As Date
            ReDim DOE_Arr(i) As Date
            ReDim MatNo(i) As String
            
            DOS_Arr(i) = objRecordSet.Fields("US1_DOSStart").Value
            DOE_Arr(i) = objRecordSet.Fields("US1_DOSEnd").Value
            MatNo(i) = objRecordSet.Fields("MAT_MatNo").Value
            
            objRecordSet.MoveNext
            i = i + 1
        Loop
            
        MsgBox LBound(DOS_Arr)
        MsgBox UBound(DOE_Arr)
        MsgBox (DOS_Arr(L))
        MsgBox (DOE_Arr(U))

The UBound seems to give me the result I want however LBound returns 12:00 AM
I thought the Bound Function was for numeric/ integer data MSDN

MY TEST DATA IS

US1_DOSStart	US1_DOSEnd
1/1/2009	6/3/2009
6/1/2009	6/1/2009
6/3/2009	1/1/2010

Any Ideas ?

[raziel_]

i think you dont understand what Ubound and Lbound do they return the top most element of an array not what the array contain at that element. for example if you have an array like this:

Dim arrStr() As String

'make an array with 21 elements from 0 to 20'
For i=0 To 20
Redim Preserve arrStr(i)
'Add Value in the array elements'
arrStr(i)= "Test: " & i
Next i

now Ubound will return the top most element of the array (20) not the value it contain like so:

'Top most element(Return: 20)'
MsgBox UBound(arrStr)
'Top most elemnt value(Return: Test: 20)'
MsgBox arrStr(Ubound(arrStr))

'Most lower element(Return: 0)'
MsgBox LBound(arrStr)
'Most lower element value(Return: Test: 0)'
MsgBox arrStr(LBound(arrStr))

hope this will help you

This post has been edited by NoBrain: 08 July 2010 - 11:19 AM

[d_rop4nme]

Right ... So that was my initial thought on looking for an alternate solution, I thought you were stating something to the contrary ?

Is there a operative MIN / MAX type function that would apply to the data type date ?

[raziel_]

can it be that this:

MsgBox (DOS_Arr(L))

return you 12:00 AM. how ever i did not see where you set L and U in your code.

[d_rop4nme]

As Integer's

        Dim Test As Date, L As Integer, U As Integer
        L = LBound(DOS_Arr)
        U = UBound(DOE_Arr)
        MsgBox (DOS_Arr(L))
        MsgBox (DOE_Arr(U))

Like you said earlier tho this is not evaluating the basis of date value, which is what i figured originally. I haven't found a MIN MAX function to evaluate dates, So I suppose I would have to write a little function to accomplish this ?

[raziel_]

or you can write it in string array. but if you get correct values from the recordset this must be correct. try using Format(DOS_Arr(L)),"mm/dd/yyyy")

[vb5prgrmr]

You will need to sort the values in the array... use your friends (yahoo, google, ask, answers, bing) to search for vb6 bubble sort. Once the values in the array are sorted, then you can know their bounds...



Good Luck

[thava]

using your select query with the min and max expression you will get the required result

select maX(US1_DOSStart) from sumDetailData.csv

will give the required result

[raziel_]

or even if you want them to be ordered like vb5prgrmr suggest use ORDER BY in the query. it all depends what you want to do.

[d_rop4nme]

Thankss for all the ideas

I ended up doing the whole thing as a SQL statement back to the database rather then the .csv file

cmd.CommandText = "Select MIN(convert(datetime, tm9user.usertype1.USR1_02_01)) from timematters9.tm9user.usertype1 WHERE tm9user.usertype1.MAT_NO =  '" & MatNo & "'"

and then the max statement

I found a nice resource for sorting algorithms I'm gonna try to work into my repertoire

Sorting Algorithms

This post has been edited by d_rop4nme: 09 July 2010 - 08:43 PM

[raziel_]

just out of curiosity what are you trying to do?

[d_rop4nme]

I do alot of VBA /VB6 stuff with Document Creation, templates, the program Time Matters is pretty heavily reliant on VBA code for document assembly ... The user generate a pretty complex mail-merge template there can be 1, 5, or 500 dates of service a sub report need's to be generated to get the first start date and the latest end date for referential purposes in a separate database ... The info was already merged out into a csv file for the mail merge part of the procedure so I initially though there would be a straightforward way to get the lowest and highest dates from the array (easy in .net) however it ended up being easier to just right the extra ADO/SQL I guess ... I do plan to learn these sorting algorithms that's pretty cool stuff, learn something every day ...

Again thanks for all the help