Adding date fields to table design

  • Thread starter Thread starter ken_d128
  • Start date Start date
K

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
 
[email protected] said:
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
Click to expand...

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!
 
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
 
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
Click to expand...

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]
 
Back
Top