Adding date fields to table design

[ken_d128]

I am attempting to make a table with 5 years of date on it
When i add Date2 field i get nothing
It runs fine with only date and week field
wanting to make
date = Monday
Date2= Tuesday
Date3= Wednesday
Date4=Thursday
Date 5 =Friday
Any help is appreciated
Thank you


Public Sub MakeTable()


Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fd As DAO.Field
Dim rs As DAO.Recordset
Dim dtmdate As Date

Set db = CurrentDb()

' Create a new table
Set td = db.CreateTableDef("tblWeek1")

' Create a new field
Set fd = New DAO.Field
fd.Name = "Date"
fd.Type = dbDate

' Add the field to the table
td.Fields.Append fd

' Create a new field
Set fd = New DAO.Field
fd.Name = "Date2"
fd.Type = dbDate

' Add the field to the table
td.Fields.Append fd

' Create a new field
Set fd = New DAO.Field
fd.Name = "WeekNo"
fd.Type = dbText

' Add the field to the table
td.Fields.Append fd

' Add the table to the database
db.TableDefs.Append td

' Open the table
Set rs = td.OpenRecordset

' Add dates and weekno
For dtmdate = #10/24/2005# To #12/31/2010# Step 7
rs.AddNew
rs!Date = dtmdate
rs!Date1 = dtmdate+1
rs!WeekNo = Year(dtmdate) Mod 10 & Right("0" & Format(dtmdate,
"ww"), 2)
rs.Update
Next
rs.Close

End Sub

 

[John Vinson]

I am attempting to make a table with 5 years of date on it
When i add Date2 field i get nothing
It runs fine with only date and week field
wanting to make
date = Monday
Date2= Tuesday
Date3= Wednesday
Date4=Thursday
Date 5 =Friday

Why?

This table structure is TOTALLY incorrectly normalized. You should have a
one to many relationship, with one date field in five records.

What is the intended purpose of this table? I'm sure there's an easier way
to go that doesn't involve this structure at all!

 

[ken_d128]

using this table for a schedule form
schedule date could be 6 months out
i got it to work had date2 instead of date1

but any advice on making simpler would be appreciated

 

[John Vinson]

using this table for a schedule form
schedule date could be 6 months out
i got it to work had date2 instead of date1

but any advice on making simpler would be appreciated

If I had any idea how your data was structured or what you were trying
to accomplish, I might be able to make some productive suggestions,
but I don't.

All I can say is that to display a date six months out from a date in
your table, you do NOT need a second table field. Instead, create a
Query with a calculated field defined by typing

Date2: DateAdd("m", 6, [Date1])


John W. Vinson[MVP]