编写一个程序来创建顺序记录 [英] Writing a routine to create sequential records

问题描述

我想编写一个程序，这将让我把日期的事件（记录）的表，它跨越翻过一个设定的时间框架和在任何情况下发生的特定日期的情况下，一个事件将被创建复制最新的前科，其中一个事件确实发生。

I would like to write a routine which will allow me to take dated events (records) in a table which span accross a set time frame and in the cases where no event took place for a specific day, an event will be created duplicating the most recent prior record where an event DID take place.

例如：如果9月4场1 = X，字段2 = Y和现场3 = Z，然后就没有发生，直到9月8日，其中字段1 = Y，字段2 = Z和现场3 = X，程序将在表中创建记录占3天，其中没有发生，并最终返回表看起来像：

For example: If on September 4 Field 1 = X, Field 2 = Y and Field 3 = Z and then nothing took place until September 8 where Field 1 = Y, Field 2 = Z and Field 3 = X, the routine would create records in the table to account for the 3 days where nothing took place and ultimately return a table looking like:

9月4日：X - Y - Z 9月5日：X - Y - Z 9月6日：X - Y - Z 9月7日：X - Y - Z 9月8日：Y - Z - X

Sept 4: X - Y - Z Sept 5: X - Y - Z Sept 6: X - Y - Z Sept 7: X - Y - Z Sept 8: Y - Z - X

不幸的是，我虽然良好的编程知识水平，不允许我在逻辑上推断在这种情况下的解决方案。我的直觉告诉我，一个循环可能是正确的解决方案，在这里，但我还是一个不知道究竟如何。我只是需要一些指导，让我开始。

Unfortunately, my level of programming knowledge although good, does not allow me to logically conclude a solution in this case. My gut feeling tells me that a loop could be the correct solution here but I still an not sure exactly how. I just need a bit of guidance to get me started.

推荐答案

在这里，你走了。

Sub FillBlanks()
    Dim rsEvents As Recordset
    Dim EventDate As Date
    Dim Fld1 As String
    Dim Fld2 As String
    Dim Fld3 As String
    Dim SQL As String

    Set rsEvents = CurrentDb.OpenRecordset("SELECT * FROM tblevents ORDER BY EventDate")
    'Save the current date & info
    EventDate = rsEvents("EventDate")
    Fld1 = rsEvents("Field1")
    Fld2 = rsEvents("Field2")
    Fld3 = rsEvents("Field3")
    rsEvents.MoveNext
    On Error Resume Next
    Do
        ' Loop through each blank date
        Do While EventDate < rsEvents("EventDate") - 1 'for all dates up to, but not including the next date
            EventDate = EventDate + 1 'advance date by 1 day
            rsEvents.AddNew
            rsEvents("EventDate") = EventDate
            rsEvents("Field1") = Fld1
            rsEvents("Field2") = Fld2
            rsEvents("Field3") = Fld3
            rsEvents.Update
        Loop
        ' get new current date & info
        EventDate = rsEvents("EventDate")
        Fld1 = rsEvents("Field1")
        Fld2 = rsEvents("Field2")
        Fld3 = rsEvents("Field3")
        rsEvents.MoveNext
        ' new records are placed on the end of the recordset,
        ' so if we hit on older date, we know it's a recent insert and quit
    Loop Until rsEvents.EOF Or EventDate > rsEvents("EventDate")
End Sub

这篇关于编写一个程序来创建顺序记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！