Generated Table 1

[gr8whitewaldron]

I need to create a table of all the dates that fall between two dates that come from a form. A query will later assign all the records a value to coincide with the date. Any idea on how I can make this?

 

[willir]

gr8whitewaldron

Are you sure you want to create a table where the field names are the specifc dates? I am going to assume not. (You would have a problem dropping and adding columns if you did for every time you run the query.)

So assuming you want to recreate records for every entry between the two dates, I would use a simple VB code assign to the "on click" event for A form. The form would have two fields
- dtStart as date
- dtEnd as date

The table would include uyour date field plus what ever else you need.

tblEvents
fldEventDate as date
plus fields for whatever else you will be associating to the date.

Since you your memo suggests a date and an event are tied together, you may want to make the index for the date field unique with no nulls.

I use something similar to this to populate some records to be filled out later. In your case, it creates teh date, and you can add the data later...

private sub your_action_button_onclick()

dim dbs as dao.database, rst as dao.recordset
dim dtStarts as date, dtEnds as date, dtCurrent
dim boFill as boolean

set dbs = currentdb()
set rst = dbs.openrecordset("tblEvents&quot

'Little bit of bullet proofing to make sure
' have information
if not isnull(me.dtStart) then
dtStarts = me.dtStart
boFill = true
else
boFill = false
end if

if bofill and not isnull(me.dtEnd) then
dtEnds = me.dtEnd
else
bofill = false
end if

if boFill
if dtEnds < dtStarts then boFill = false
end if


if boFill
' We have two valid dates
dtCurrent = dtStarts

do while dtCurrent < dtEnds + 1

rst.addnew
rst!fldEventDate = dtCurrent
rst.update

dtCurrent = dtCurrent + 1

loop

end if

rst.close
dbs.close

end sub

This will create a record for each date from start to end. If the date field is indexed not allow duplicates, then no entry will occur for duplicate dates.

Needless to say, there are variations on this depending on programming style and your needs.

Richard

 

[gr8whitewaldron]

Wow, thanks a lot, I'll see what I can do with this.

 

[gr8whitewaldron]

I put in your code, got it so that it runs and doesn't come up with errors, but it does not alter tblEvents. I have a form with 2 date boxes, Name is dtStart and dtEnd. Any thoughts?

 

[gr8whitewaldron]

Sorry I've posted on my own thread so much. I took out the bullet proofing stuff and it works great. Thanks again.

-Matt