-
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.
-
Re: sequential dates
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.
-
Re: sequential dates
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.
-
Re: sequential dates
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
-
Re: sequential dates
Where are you putting the rs into fields? That's probably where you need to format for region.
-
Re: sequential dates
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
-
Re: sequential dates
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.
-
Re: sequential dates
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.
-
Re: sequential dates
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
-
Re: sequential dates
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)
-
Re: sequential dates
Simply make the datatype of Weeks as DateTime and use:
Code:
!Weeks = DTPicker1.Value
Let it store in the system format.
-
Re: sequential dates
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
-
Re: sequential dates
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
-
Re: sequential dates
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
-
1 Attachment(s)
Re: sequential dates
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.
-
Re: sequential dates
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
-
Re: sequential dates
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
-
Re: sequential dates
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
-
Re: sequential dates
Same way. I used Dates and added 7 days to get the range, and it worked
-
Re: sequential dates
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
-
Re: sequential dates
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]
-
Re: sequential dates
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.
-
Re: sequential dates
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.