filling in empty table 
Author Message
 filling in empty table

Group:

I have an empty table, the primary key of which is a date field.  I want to
fill in about 200 records in this table that consist of all the weekdays
between the dates of 8/29/01 and 6/6/02.  Any ideas on a VBA procedure to do
this fast and easy?

Thanks in advance,
Neal



Sun, 18 Jan 2004 01:15:22 GMT  
 filling in empty table
Aircode:

Function MakeData()
Dim dtWhen As Date
Dim iDOW As Integer
Dim rs As DAO.Recordset

Set rs = CurrentDb().OpenRecordset("YourTable",, dbAppendOnly)

For dtWhen = #8/29/01# To #6/6/02#
  iDOW = WeekDay(dtWhen)
  If iDOW > 1 And iDOW < 7 Then  'Not Sundays or Saturdays.
    rs.AddNew
      rs!SomeDateField = dtWhen
    rs.Update
  End If
Next

rs.Close
Set rs = Nothing
End Function

Quote:

> Group:

> I have an empty table, the primary key of which is a date field.  I want to
> fill in about 200 records in this table that consist of all the weekdays
> between the dates of 8/29/01 and 6/6/02.  Any ideas on a VBA procedure to do
> this fast and easy?

> Thanks in advance,
> Neal

--
Tips for MS-Access users at:
        http://odyssey.apana.org.au/~abrowne/
Perth, Western Australia.


Sun, 18 Jan 2004 21:15:41 GMT  
 filling in empty table
This should work

------start code--------------
Sub Filltable()

Dim DB As Database, RS As Recordset, mydate As Date

Set DB = CurrentDb
Set RS = DB.OpenRecordset("datesTEMP")
With RS
    mydate = #8/29/01#

Do Until mydate > #6/6/02#
If WeekDay(mydate) <> 1 And WeekDay(mydate) <> 7 Then
    .AddNew
    !weekdate = mydate
    .Update
End If
mydate = mydate + 1
Loop
End With
MsgBox "done"
End Sub
----------end code---------------

Quote:

>Group:

>I have an empty table, the primary key of which is a date field.  I want to
>fill in about 200 records in this table that consist of all the weekdays
>between the dates of 8/29/01 and 6/6/02.  Any ideas on a VBA procedure to
do
>this fast and easy?

>Thanks in advance,
>Neal



Mon, 19 Jan 2004 03:42:27 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. fill in empty table cells

2. Empty resultset when adding to empty table : error

3. Empty tables in a multi-tables query

4. Empty tables in a multi-tables query

5. Fill field in one table from field in another table

6. Fill a date type with empty value

7. Data Grid Empty when adUseServer - but filled w/adUseClient

8. filling an empty treeview by using a populated one

9. linked table empty

10. Problem with Empty or Null fields in a Table on a Report

11. Append Query on empty table

12. Empty Tables and Errors

 

 
Powered by phpBB® Forum Software