sequential dates

Hello
I’m using VB 6 as a front end to an Access ’03 db. I have a dtpicker to allow the user to select a date and then some other data to add a record. Problem is when the dates are recalled they are out of order. For example the date 3/8/2008 is considered later than 3/12/2008. I assume because it sees the 8 from the day portion as higher than 1 from the 12. It’s doing the same thing in my comboboxes that display the starting and ending date when choosing a range of data to display.

To populate the chart at startup I use…

Code:

---

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 * From tbliface Order By [Weeks}", cn, adOpenDynamic, adLockOptimistic
        ‘etc

---

Is this an inherent problem to using the dtpicker control and what can I do about it?
Any ideas are appreciated.

I found that I was using text for the type in Access.
Now that I changed it to Date/Time it isn't displaying anything in my chart.

I'm using a variant

Code:

---

Dim Graph(1 To 8, 1 To 2) As Variant

---

for the array and then...

Code:

---

'  set the column headings
Private Sub colHeadings()
    Graph(1, 1) = "Analyzer"
    Graph(2, 1) = "Humphrey"
    Graph(3, 1) = "Accumulator"
    Graph(4, 1) = "Pump"
    Graph(5, 1) = "Regulator"
    Graph(6, 1) = "A/D card"
    Graph(7, 1) = "NDF"
    Graph(8, 1) = "Solenoid"
End Sub

'  populate chart from the database
Private Sub graphData()
    Graph(1, 2) = Analyzer
    Graph(2, 2) = Humphrey
    Graph(3, 2) = Accumulator
    Graph(4, 2) = Pump
    Graph(5, 2) = Regulator
    Graph(6, 2) = Card
    Graph(7, 2) = NDF
    Graph(8, 2) = Solenoid
    MSChart1.ChartData = Graph
End Sub

---

I would have thought a variant could handle anything
But I've been wrong before.

What does that code have to do with the DATE field? Post some code. You may need to use FORMAT() before you can graph it.

this is what is populating my chart control

Code:

---

rs.Open "Select * From tbliface Order By [Weeks]", cn, adOpenDynamic, adLockOptimistic

---

there is more code but I'll try to post what is relevant

Steve

Where are you putting the rs into fields? That's probably where you need to format for region.

Not sure I understand. If you are asking how I am saving the rs.
Like this...

Code:

---

Private Sub cmdSave_Click()
  Dim mySQL As String

    With rs
      .AddNew
      !Weeks = DTPicker1.Month & "/" & DTPicker1.Day & "/" & DTPicker1.Year
      !Analyzer = Analyzer
      !Humphrey = Humphrey
      !Accumulator = Accumulator
      !Pump = Pump
      !Regulator = Regulator
      !Card = Card
      !NDF = NDF
      !Solenoid = Solenoid
      .MovePrevious
    End With
  'insert into table
  mySQL = "INSERT INTO tbliface([Weeks], Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF,Solenoid) VALUES('" & DTPicker1.DataField & "');"
    Debug.Print mySQL
  cboStartLoad                    ' load combobox for start date search
  cboEndLoad                      ' load combobox for end date search

End Sub

---

I meant the other way. Where do you say Analyzer = !analyzer

If you get it from a grid, then it's in text format again.

thanks for the reply
I don't get it from a grid. I just...

Code:

---

rs.MoveFirst
Set MSChart1.DataSource = rs
MSChart1.Refresh

---

I hope that's what you are asking. But from this I'm not sure where the format would fit in.

Place a breakpoint (by pressing F9 on the line in question)

Code:

---

    Graph(1, 2) = Analyzer

---

then you can hover over the variables to see what they are. I'm betting you'll need

Code:

---

    Graph(1, 2) = Format(Analyzer,"mm/dd/yy")

---

for each variable, to convert it into a value that can be graphed

Your field "Weeks"

If this is defined as a Text Field then you will always have your existing problem

You can avert the problem by doing

Code:

---

!Weeks = DTPicker1.Year & "/" & DTPicker1.Month & "/" & DTPicker1.Day

---

Alternately you cound define "Weeks" as a Date Type rather than a Text Type and it will sort correctly when selected through SQL (ie, Order by Weeks)

Simply make the datatype of Weeks as DateTime and use:

Code:

---

    !Weeks = DTPicker1.Value

---

Let it store in the system format.

Hello
I tried this...but it didn't populate the chart. The same for the rest of the tries.

Code:

---

!Weeks = DTPicker1.Year & "/" & DTPicker1.Month & "/" & DTPicker1.Day

---

and this...

Code:

---

!Weeks = DTPicker1.Value

---

tried this...

Code:

---

'  populate chart from the database
Private Sub graphData()
    Graph(1, 2) = Format(Analyzer, "mm/dd/yyyy")
    Graph(2, 2) = Format(Humphrey, "mm/dd/yyyy")
    Graph(3, 2) = Format(Accumulator, "mm/dd/yyyy")
    Graph(4, 2) = Format(Pump, "mm/dd/yyyy")
    Graph(5, 2) = Format(Regulator, "mm/dd/yyyy")
    Graph(6, 2) = Format(Card, "mm/dd/yyyy")
    Graph(7, 2) = Format(NDF, "mm/dd/yyyy")
    Graph(8, 2) = Format(Solenoid, "mm/dd/yyyy")
    MSChart1.ChartData = Graph
End Sub

---

no errors it just left the chart blank

Not sure what you are trying to achieve but I dont think using dates as values in your graph will work

If you want to achieve a graph based on dates you should do as follows

1) Change the date to a Julian Day value
2) Use the Julian Day as the value you pass to the graph

Example

Date 12/24/2006 would become (2006 * 365) + (12 * 30) + 24 = 732,574

ie, It becomes a number of Days from 1/1/00

By doing this you have a relevant number to graph

If a number like 732,574 is too large, just deduct 700,000 from the result of each date conversion, then you will be working with more viewable values

Now if you need to be exact to the day count then you will need to Sum your years and add 1 day for every leap year - Sum your Months using exact day numbers 31,28,31,30 etc, then add the day of the month

BUT for the purposes of your graph I dont think it will make a scrap of difference when viewed as a graph

Did you step thru? Should see the problem if you change the value and run it again.

Code:

---

Private Sub Command1_Click()
  With MSChart1
      ' Displays a 3d chart with 8 columns and 8 rows
      ' data.
      .chartType = VtChChartType3dBar
      .ColumnCount = 8
      .RowCount = 8
      For column = 1 To 8
        For row = 1 To 8
            .column = column
            .row = row
            .Data = row * 10
        Next row
      Next column
      ' Use the chart as the backdrop of the legend.
      .ShowLegend = True
      .SelectPart VtChPartTypePlot, index1, index2, _
      index3, index4
      .EditCopy
      .SelectPart VtChPartTypeLegend, index1, _
      index2, index3, index4
      .EditPaste
  End With
End Sub

---

I think I've confused everyone. I'm not trying to graph a date value but the Row labels are dates. Actually each group of defects is a group that is accumulated over a weeks time.

I changed the property in the database from text to Date/Time.

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

Code:

---

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

---

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...?

Code:

---

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

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

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

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

Code:

---

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

this is getting a chart displayed at startup...

Code:

---

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...

Code:

---

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

Code:

---

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

Code:

---

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

---

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

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.

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.