DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 31

Thread: sequential dates

Hybrid View

  1. #1
    Join Date
    Sep 2005
    Posts
    106

    sequential dates -- resolved

    Hello
    Im 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. Its 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.
    Last edited by RipIT; 03-03-2008 at 07:42 PM.

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    It has nothing to do with the dtpicker, the problem is how you're storing the dates in the database. If you store them as text, Access returns them in "alphabetical" order. You need to store them in a Date/Time column in order for them to sort correctly.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Sep 2005
    Posts
    106
    thanks Phil you are right 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.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    How are you using the arrays in your code to graphically display the data?

  5. #5
    Join Date
    Sep 2005
    Posts
    106
    I see what you are saying I'm barking up the wrong tree here. It must have more to do with how I am displaying the chart.

    I just use..
    Code:
    rs.MoveFirst
    Set MSChart1.DataSource = rs
    MSChart1.Refresh
    Someone said maybe using a format command would help. But from this I'm not sure where the format would fit in.

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Format would fit in for a specific date, but I don't see where your recordset is singling out a specific date.

    Unless, of course, you are still using my LoadChart Sub and the else (not shown in your first post) has a WHERE clause that does specify a specific date.

  7. #7
    Join Date
    Sep 2005
    Posts
    106
    Yes, I am using your routine..
    Code:
    strSQL = "Select * From tbliface Where [Weeks] =  '" & frmMain.cboStart.Text & "' Order By [Weeks]"
            rs.Open strSQL, cn
    not sure how to use the format command.
    This doesn't do anything
    Code:
    Format(Weeks)
    or this..
    Code:
    Order By format(Weeks(),"mm dd , yyyy")

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Weeks is a field in your database table, correct?

    It would appear like you need to format your combo box (which, presumably, contains dates
    Code:
    "Select * From tbliface Where [Weeks] =  '" & Format(frmMain.cboStart.Text, "mm/dd/yyyy") & "' Order By [Weeks]"

  9. #9
    Join Date
    Sep 2005
    Posts
    106
    thanks for the reply
    I assume that this is what is needed..
    Code:
    strSQLSearch = "Select [Weeks] From tbliface Where [Weeks] =  '" & Format(frmMain.cboStart.Text, "mm/dd/yyyy") & "' Order By [Weeks]"
    I replaced "Select * -- with -- "Select [Weeks]
    unfortunately I'm getting the error "Data type mismatch in criteria expression"
    Code:
    strSQLSearch = "Select [Weeks] From tbliface Where [Weeks] =  '" & Format(frmMain.cboStart.Text, "mm/dd/yyyy") & "' Order By [Weeks]"
       'strSQLSearch = "SELECT [Weeks] From tbliface Order By [Weeks]"
    
       Set oRS = New Recordset
       oRS.Open strSQLSearch, cn, adOpenForwardOnly, adLockReadOnly ' error here
    I thought it may have helped trying this...
    Code:
    'oRS.Open strSQLSearch, cn, adOpenDynamic, adLockOptimistic
    but no luck

    I ran it and cursored over..
    Code:
    (frmMain.cboStart.Text.....
    and the word "Starting" pops up. That is what I had in the text property of the combobox. I deleted it and "" pops up instead

    Yes, Weeks is a field in my database table
    Last edited by RipIT; 02-24-2008 at 11:15 AM.

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Is the field in your database that stores the date a text field or a date/time field?

  11. #11
    Join Date
    Sep 2005
    Posts
    106
    It was Text but has been changed to Date/Time type.

  12. #12
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Well, I wasn't suggesting that you change it. I just wanted to know what it was.

    Sometimes how you pass variables to a field depends on what the field type is.

  13. #13
    Join Date
    Sep 2005
    Posts
    106
    Yes, I changed it to Date/Time type.
    I understand it won't sort correctly if it is text type. At least not how I want it to sort.
    There also seems to be a particular order the date elements (mmddyyyy)need to be in to sort properly.

    I guess I've got two issues going on. Loading the date comboboxes correctly and loading the chart at startup. At startup the dates are out of order.

    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?

    I tried this Select statement...
    Code:
    rs.Open "Select Weeks, Analyzer, Humphrey, Accumulator, Pump, Regulator, Card, NDF,Solenoid From tbliface Order By [Weeks]", cn, adOpenDynamic, adLockOptimistic
    Maybe I should try to get it to load the chart at startup then deal with the comboboxes when that is straightened out.
    It's not alot different than I've been trying.
    But not sure where to go from here
    Last edited by RipIT; 02-28-2008 at 01:16 PM.

  14. #14
    Join Date
    Sep 2005
    Posts
    106
    The comboboxes are working better. Part of the issues with those is
    the status between text and date type. So I used some vars and came up with this...

    Code:
    Private Sub DateRange() 
         
        Dim DateFrom As Date ' added this part
        Dim DateTo As Date 
        DateFrom = cboStart.Text 
        DateTo = cboEnd.Text 
             
        ' 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   ' added this
            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
    no longer getting the situation where it believes 3/15/2007 is older than 3/6/2007. Now if the chart would display at startup or after choosing a date
    range to display.

  15. #15
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by RipIT
    Now if the chart would display at startup or after choosing a date range to display.
    What do you have in the Form Load of your startup form?

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