Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    Post Unanswered: VBA Question on how to fill in numbers

    I have a database that has records that are kept by a time stamp; the problem is that the data records are only those with ideas that have input associated with that time. I need to set up a btton that inputs records into the table for those times not in the table. Here is an example of data before and after:

    Before button
    time --- field_1---field_2---field_3---field_4
    10:00---a---------b----------c--------d
    10:01---1---------2----------3--------4
    10:03---a---------k----------d--------i
    10:07---u---------8----------s--------5
    10:08---w---------I----------q--------3


    After button
    time --- field_1---field_2---field_3---field_4
    10:00---a---------b----------c--------d
    10:01---1---------2----------3--------4
    10:03---a---------k----------d--------i
    10:04
    10:05
    10:06
    10:07---u---------8----------s--------5
    10:08---w---------I----------q--------3

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Here is what I came up with:

    Code:
    Public Sub UpdateData()
    
        Dim strTableName As String
        Dim rstCurrentData As New ADODB.Recordset
        Dim rstNewData As New ADODB.Recordset
        Dim strSQL As String
        Dim datStart As Date
        Dim datEnd As Date
        Dim datTime As Date
        
        datStart = "10:00"
        datEnd = "11:00"
        datTime = datStart
        
        'Change this to your tablename
        strTableName = "tblStuff"
        
        'Change TimeStamp to your field name
        strSQL = "SELECT * FROM " & strTableName & " ORDER BY TimeStamp"
        
        rstCurrentData.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        rstNewData.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        
        'Loop through the start and end time, incrementing by a minute each time through the loop
        While datEnd > datTime
       
            'If you have run out of current data to look at then just add the new record
            If Not rstCurrentData.EOF Then
            
                If datTime < rstCurrentData!TimeStamp Then
            
                    rstNewData.AddNew
                    rstNewData!TimeStamp = datTime
                    rstNewData.Update
                    
                Else
            
                    If Not rstCurrentData.EOF Then rstCurrentData.MoveNext
                
                End If
                
            Else
            
                rstNewData.AddNew
                rstNewData!TimeStamp = datTime
                rstNewData.Update
                
            End If
                
            datTime = DateAdd("n", 1, datTime)
            
        Wend
            
        'Cleanup
        rstNewData.Close
        Set rstNewData = Nothing
        
        rstCurrentData.Close
        Set rstCurrentData = Nothing
        
    End Sub

  3. #3
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    I thing that I leftout some valued information

    I think that I left out some information that is needed; the timestap is in Coordinated Universal Time (UTC) the way the fields come in is as follows.

    Field Name----------------Data Type

    Time ---------------------Double


    I am trying to work to get it to work based on it being a number and not a true time. the other thing is I can't get past the:
    Dim rstCurrentData As New ADODB.Recordset setting this variable. I get the errror message of:
    Compile error:
    User-defined type not defined

  4. #4
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    working sort of:

    Hey thanks again for the assistence, I was able to get the button working but the it seems to be adding about 50 enteries between each time. In the database that I am working on as a development platform the time stamps are in whole numbers but the filled in feilds are returning times with .00563 and things like that. That is why I am getting so many fields added between times. an example of this is:

    100023
    100024
    100025
    100026.00265
    100026.004125
    100026.005894
    100026.007548
    100027
    100028

    As I stated in the previous post the time will are using is UTC time to the .0000 place.

    Do you have any ideas on how to fix that.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •