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.