Append date query

[dan-cat]

Hello,

I think what I am trying to do is quite simple but I can't work it out.
I have an empty table with one field, primary key called SlotDate.
All i want to do is append the dates between say 01/01/2003 and 31/12/2103 in to the table.

At the moment I am creating the values in excel and importing but I wondered if you could do this via a query.

ThankYou

Dan

 

[Rich]

Are you saying that you want to fill the table with all the dates between 01/01/2003 and 31/12/2103

 

[dan-cat]

Yes thats right

 

[Rich]

This would seem to indicate that you'll be storing redundant data, if you give some idea of the reason behind wanting to do this there might be alternatives, other than that I'll post the function for you

 

[Mile-O]

31/12/2103

That's what I call "planning ahead"

 

[dan-cat]

Mike: Yes maybe I'm getting a bit too futuristic with this.

Rich: OK I'll try to explain,
There are three tables.
tblDoctor with fields DoctorId (primary),DoctorName
tblDate with field SlotDate only
tblSlotDay with fields DoctorId and SlotDate (both primary keys) and various other fields.

What I am looking to do is whenever the user creates a new record within tblDoctor, appropriate records will appended to tblSlotDate.DoctorId and tblSlotDate.SlotDate.

This is to allow each doctor to have its own record for each date within tblDate.

Hope that makes sense.

 

[Jon K]

Try this. You can run the code from the On Click Event of a command button on a form.

Code using DAO (for Access 97):-

Private Sub Command0_Click()

On Error GoTo Err_handler
   
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim dDate As Date
   
   Set db = CurrentDb()
   Set rs = db.OpenRecordset("Select SlotDate from tblDate")
   
   dDate = DateSerial(2003, 1, 1)
   
   Do While dDate <= DateSerial(2003, 12, 31)
     rs.AddNew
     rs!slotdate = dDate
     rs.Update
     dDate = dDate + 1
   Loop
   
   MsgBox "Dates filled."
   Set rs = Nothing
   Set db = Nothing
   Exit Sub
   
Err_handler:
  MsgBox Err.Description

End Sub

Code using ADO (for Access 2000, 2002):-

Private Sub Command0_Click()

On Error GoTo Err_handler

   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim dDate As Date
   
   Set rs = New ADODB.Recordset
   Set cnn = CurrentProject.Connection
   rs.Open "Select SlotDate from tblDate", cnn, adOpenDynamic, adLockOptimistic
   
   dDate = DateSerial(2003, 1, 1)
   
   Do While dDate <= DateSerial(2003, 12, 31)
     rs.AddNew
     rs!slotdate = dDate
     rs.Update
     dDate = dDate + 1
   Loop
   
   MsgBox "Dates filled."
   Set cnn = Nothing
   Set rs = Nothing
   Exit Sub
   
Err_handler:
  MsgBox Err.Description
  
End Sub