CODEWITHSUNDEEP
arraysloopsms-accessvbarecord-count
Kelly K.
Kelly K.

Reputation: 587

How do i looping through my recordset using an array?

I would like my function to open the table, and return 1/1/2013 for recordcount 1, 1/2/2013 for recordcount 2 ... 1/20/2013 for record count 20.

so far I have the following code but it only return 1/2/2013 and I have 24 records: (My recordcount will vary from 22 -30 each month)

Public Function DDate() As Date

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim icounter As Integer
Dim UpBound As Long


Set db = CurrentDb()
Set rs = db.OpenRecordset("tblDDate", dbOpenDynaset)


    If Not (rst.BOF And rst.EOF) Then
        rst.MoveFirst

        UpBound = rs.RecordCount

    Do Until rs.EOF = True

        For icounter = 1 To UpBound
            DDate = DateAdd("m", icounter, "1/1/2013")
            rst.MoveNext
        Next icounter
    Loop
    End If

rs.Close
db.Close

 End Function

Upvotes: 0

Views: 705

Answers (1)

John Bingham
John Bingham

Reputation: 2006

Your comment above says that the table doesnt contain a date but that you are trying to add a date field to the table.

Unfortunately you cannot add a new field (column) to a table like this. You'll need to do that either in design view, or running an ALTER statement on the database at runtime:

ALTER TABLE myTableName ADD COLUMN dateColumnName Date Null;

then if you want to set the value of the column, hopefully your table has a unique id with the datatype counter(?) I'd recommend you to use an update query:

UPDATE myTableName SET dateColumnName = CDate("1/" & trim(uniqueIDColumnName) & "/2013");

Upvotes: 1

Related Questions