DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31

Thread: sequential dates

  1. #16
    Join Date
    Sep 2005
    Posts
    106
    Hello
    The chart is displaying at startup now with this...
    And it's even in order.
    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
        Exit Sub
          
    End Sub
    but the date range routine is not working.
    This is what I'm using to load the combobox with dates...
    Code:
    Public Sub cboStartLoad() 
       Dim oRS As Recordset 
       Dim strSQLSearch As String 
       Dim strStart As String 
    
       strStart = LCase(frmMain.cboStart.Text & vbNullString) 
       If Not (strStart = vbNullString Or strStart = "Starting") Then 
       'run the query 
            'strSQLSearch = "SELECT Format(Weeks,'mm/dd/yyyy') From tbliface Order By Format(Weeks,'mm/dd/yyyy')" 
            strSQLSearch = "Select [Weeks] From tbliface Where [Weeks] = #" & Format(frmMain.cboStart.Text, "mm/dd/yyyy") & "# Order By [Weeks]" 
            Debug.Print strSQLSearch 
        Set oRS = New Recordset 
        oRS.Open strSQLSearch, cn, adOpenForwardOnly, adLockReadOnly ' error here 
       End If 
    
    Do Until oRS.EOF 
          frmMain.cboStart.AddItem oRS("Weeks") 
          oRS.MoveNext 
       Loop 
       oRS.Close 
    End Sub
    but it's erroring with...
    "Syntax error in date in query expression '[Weeks] = #Starting#'
    Last edited by RipIT; 03-01-2008 at 06:43 PM.

  2. #17
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Code:
       strStart = LCase(frmMain.cboStart.Text & vbNullString) 
       If Not (strStart = vbNullString Or strStart = "Starting") Then
    This is your problem. You lower case the value in cboStart but you test for a mixed case value.
    Try it with this change:
    Code:
       If Not (strStart = vbNullString Or strStart = "starting") Then

  3. #18
    Join Date
    Sep 2005
    Posts
    106
    Yes, thank you. That worked

  4. #19
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    So, does this do it for this one, or do you still have issues hanging?

  5. #20
    Join Date
    Sep 2005
    Posts
    106
    Hello
    Yes, there are issues. It loads the chart nicely at startup but the load data with a date range chosen from two comboboxes isn't working.
    Code:
    Private Sub DateRange()
        'On Error GoTo DateRangerr
        
        '  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 cboStart.Text <= cboEnd.Text Then
            rs.MoveFirst
            Set MSChart1.DataSource = rs
            MSChart1.Refresh
        Else
            MsgBox "The End date must be equal or larger than the Start date.", vbExclamation
        End If
    
    End Sub
    this used to work when the dates were text type in the database. Now they are Date/Time it isn't working. It doesn't error it just is giving a blank chart.
    It was nice while it lasted but it wouldn't sort correctly being a text type.

  6. #21
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Are you getting errors or is it just not working right?

  7. #22
    Join Date
    Sep 2005
    Posts
    106
    There aren't any errors it just won't pop the chart from a date range the user selects.

  8. #23
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Ok, zip it up again, and attach it and I'll take a look.

    I can't guarentee I'll be able to get to it today (but I might). If not, tomorrow for sure.

  9. #24
    Join Date
    Sep 2005
    Posts
    106
    thanks Hack, any help is appreciated.
    You've already helped alot!
    Attached Files Attached Files

  10. #25
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    No problem.....I'll get back to you!

  11. #26
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    Your problem is with the chart control. It wants the dates, [Weeks], in text format. When you used the Format() function in your original SQL statement, in the LoadChart routine, You essentually made the first field a calculated field of type text, so the chart control was happy. But in the range selection routine you used "SELECT *..." so Weeks is selected but as a date/time data type which the chart control can't handle.
    Try using this Sql Statement in your DateRange() routine:
    Code:
        sSQL = "Select Format(Weeks,'mm/dd/yyyy'), Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF, Solenoid From tbliface " _
            & " WHERE [Weeks] >= #" & cboStart.Text & "#" _
            & " And [Weeks] <= #" & cboEnd.Text & "#" & " ORDER BY [Weeks]"

  12. #27
    Join Date
    Sep 2005
    Posts
    106
    Yes, that did it. Fantastic.
    Thanks Ron. I used the same code or almost the same to cure the chart by category routine that wasn't displaying for the same reasons.

    This is resolved thanks to you and Hack. No way could I have done this on my own

  13. #28
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    One last note. You might try modifying the optChart_Click() routine to Hide the Autospin and Stop buttons when the chartType is a 2D style chart. Also you should add a call to the cmdStopRotate_Click() routine, in case the user tries to change the chart type while the chart is spinning. Something like this:
    Code:
    Private Sub optChart_Click(Index As Integer)
      cmdStopRotate_Click
      Select Case Index
        Case 0
          MSChart1.chartType = VtChChartType2dBar
        Case 1
          MSChart1.chartType = VtChChartType2dLine
        Case 3
          MSChart1.chartType = VtChChartType3dBar
        Case 4
          MSChart1.chartType = VtChChartType3dLine
        Case 5
            MSChart1.chartType = VtChChartType3dStep
      End Select
      Select Case MSChart1.chartType
      Case VtChChartType3dBar, VtChChartType3dLine, VtChChartType3dArea, VtChChartType3dStep, VtChChartType3dCombination
        cmdRotate.Visible = True
        cmdStopRotate.Visible = True
      Case VtChChartType2dBar, VtChChartType2dLine, VtChChartType2dArea, VtChChartType2dStep, VtChChartType2dCombination, VtChChartType2dPie, VtChChartType2dXY
        cmdRotate.Visible = False
        cmdStopRotate.Visible = False
      End Select
      
    End Sub

  14. #29
    Join Date
    Sep 2005
    Posts
    106
    thanks for the suggestion. I had considered hiding the rotate and stop buttons. But I like the idea of adding the cmdStop routine in the optionbutton/select case procedure.

    thanks again, good stuff!

  15. #30
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    And I thank Ron as well....I felt a little guilty 'cause I couldn't get to looking at your code yesterday, but it seems everything turned out OK anyway.

Similar Threads

  1. java dates
    By jonneymendoza in forum Java
    Replies: 0
    Last Post: 04-21-2006, 10:18 AM
  2. Data access and dates (simple question?!)
    By Neil Martin in forum VB Classic
    Replies: 1
    Last Post: 08-15-2002, 05:09 PM
  3. Using Data Access & dates (simple question!)
    By Neil Martin in forum VB Classic
    Replies: 4
    Last Post: 08-15-2002, 11:16 AM
  4. Multiple dates for one database entry
    By Seg in forum ASP.NET
    Replies: 5
    Last Post: 12-19-2001, 12:50 AM
  5. How to compare dates to get desired results... ?
    By Jaikumar Sharma in forum VB Classic
    Replies: 2
    Last Post: 04-01-2000, 12:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center