sequential dates

[George1111]

You mention you are having problems getting the dates to arrange in date order - So you want the HEADINGS Sorted by Date ?

yyyymmdd is the only way to sort dates if they are in text format

mmddyyyy will never sort correctly in text format
ddmmyyyy will never sort correctly in text format


Changing from Text format to Date format will work, so long as your select statement does the sorting

Select value1, value2, value3, date1 from mytable Order by date1

[codeUp]

Headings sorted by date...

Yes, I believe they are called Row Lables but I think we are talking about the same thing.

A select statement something like this...?

rs.Open "Select Weeks, Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF,Solenoid From tbliface Order By [Weeks]", cn, adOpenDynamic, adLockOptimistic

It's not loading my chart at startup. It's just a blank chart.
I guess I've got two issue loading the date comboboxes correctly and loading the chart at startup.

The format for the dates (Weeks field) in my database is mmddyyyy.
Is this a problem, the order of these date elements, if I'm using Date/Time type in my db?

thanks for any help.
Steve

[George1111]

mmddyyyy should look like a value of 12/23/2008 if you have US Date Format set in your Control Panel

This is the correct format for sorting also (... Order by Weeks)

Watch your ComboBoxes where you check if The From Combo.Text < the To Combo.Text

This will produce a wrong result as once your dates are in a compbo box, they become text format which means that 12/23/1999 if greater than 01/23/2008 (12 is greater than 01 !)

What you need to do is create 2 date working variables eg

DateFrom as Date
DateTo as Date

Then DateFrom = Combo1.text ..... DateTo = Combo2.text

If DateFrom > DateTo then etc etc

As for your Graph which shows no values, here I cant help as I have no idea how Microsoft Graph values are loaded

I would say though that simply doing ....

MsGraph.RecordSource = RS wont do the trick

You need to play with getting values into the graph

I would simply move some test values in to see if I could get the graph to work

eg, MsGraph(1).Value = 123 (This is only an example as I HAVE NO IDEA WHAT SYNTAX MSGRAPH USES)

Once you understand how it works - only then get fancy and try loading from a datafile - load the variables to memory then populate the graph from your memory variables

[dglienna]

Same way. I used Dates and added 7 days to get the range, and it worked

[codeUp]

thanks for your help
It made a big difference.
I used...

Private Sub DateRange()
    
    Dim DateFrom As Date
    Dim DateTo As Date
    DateFrom = cboStart.Text
    DateTo = cboEnd.Text
    
    '  Close the record set if open
    If rs.State <> adStateClosed Then
        rs.Close
    End If
    
    '  query the range of chart data
    Set cmd.ActiveConnection = cn
    
    cmd.CommandText = "SELECT * FROM tbliface WHERE [Weeks] >= #" & cboStart.Text & "#" _
        & " And [Weeks] <= #" & cboEnd.Text & "#" & " ORDER BY [Weeks]"
    
    rs.CursorLocation = adUseClient
    rs.Open cmd, , adOpenStatic, adLockBatchOptimistic
        
    If DateFrom <= DateTo Then
        rs.MoveFirst
        Set MSChart1.DataSource = rs
        MSChart1.Refresh
    Else
        MsgBox "The End date must be equal or larger than the Start date.", vbExclamation, "Quality"
    End If
End Sub

that straightened out the combobox predicament.
sometimes I need a nudge in the right direction to get going
or in this case a ton of bricks.

Now I need to get something to display at startup.
Boy it's always something

[codeUp]

this is getting a chart displayed at startup...

Public Sub LoadChart(pstrChoice As String)
    'this sub will populate our chart depending on what we pass to it when it is called
    Dim strSQL As String
    Set rs = New ADODB.Recordset
    
    'Setting Initial data for recordset
    If pstrChoice = "All" Then 'we want all records in the database
    rs.Open "Select Format(Weeks,'mm/dd/yyyy'), Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF, Solenoid From tbliface Order By Format(Weeks,'mm/dd/yyyy')", cn, adOpenDynamic, adLockOptimistic
     
        Else 'we only want the record for a specific period
        strSQL = "Select * From tbliface Where Weeks = '" & frmMain.cboStart.Text & "' Order By [Weeks]"
        rs.Open strSQL, cn
    End If
    
    frmMain.MSChart1.Width = 12000      '   chart load routine
    frmMain.MSChart1.ShowLegend = True
    frmMain.MSChart1.Title.VtFont.Size = 12
    frmMain.MSChart1.Title = "Interface Weekly Defects"
    rs.MoveFirst
    
    Set frmMain.MSChart1.DataSource = rs
    frmMain.MSChart1.Refresh
    
    Call frmMain.Show
    
End Sub

but my routine to display a selected range of dates from two comboboxes is not working.

using this...

Public Sub cboStartLoad()
   Dim oRS As Recordset
   Dim strSQLSearch As String

   strSQLSearch = "SELECT Weeks From tbliface Order By Weeks"   ' no error does not pop chart
           
   Set oRS = New Recordset
   oRS.Open strSQLSearch, cn, adOpenForwardOnly, adLockReadOnly

   Do Until oRS.EOF
      frmMain.cboStart.AddItem oRS("Weeks")
      oRS.MoveNext
   Loop
   oRS.Close
End Sub

it populates the combobox just fine
and this for the ending date

Public Sub cboEndLoad()
   Dim oRS As Recordset
   Dim strSQLSearch As String

   strSQLSearch = "SELECT [Weeks] From tbliface Order By [Weeks]"

   Set oRS = New Recordset
   oRS.Open strSQLSearch, cn, adOpenForwardOnly, adLockReadOnly

   Do Until oRS.EOF
      frmMain.cboEnd.AddItem oRS("Weeks")
      oRS.MoveNext
   Loop
   oRS.Close
End Sub

again it populates just fine but it leaves a blank chart. I have it triggering on the click event of the ending combobox

Private Sub cboEnd_Click()
'   display results for a range of dates
    DateRange
End Sub

it's calling dateRange routine as you can see
[code]

[Twodogs]

Try calling the code from something other than the _click() event - perhaps the 'onlostfocus'. Click event will be fired when you click in the combo box, which is perhaps not exactly what you want.

[dglienna]

I set up two blank dateboxe, and let the user select StartDate, which populated the EndDate with the same info. As soon as the EndDate was clicked it generated more data.