Creating a table on database open

[steve]

I want Access to create a table when the database opens.
The table will have one attribute [date] and include the
date the db is opened and the following 13 dates.

Any help is greatly appreciated.

 

[John Spencer (MVP)]

Why? For what purpose? Not being picky, but knowing the reason may mean a
better solution can be suggested.

I would suggest having a table with numbers from 1 to 13 (or even higher) in it
and then use that in a query.

Table: tblNumbers
Field: fldNumbers (unique with numbers from 1 to 13 or whatever limit you need)

Then to get the all the dates you need is a query like:

qry13Dates:
SELECT DateAdd("d",fldNumbers,Date()) as MyDates
FROM tblNumbers
WHERE fldNumbers <= 13
ORDER By DateAdd("d",fldNumbers,Date())

You can use this query whereever you would have used the table.

Note that my example used a name for the field that is not Date. Date is a
reserved word in Access (I used it to build the query) and will tend to cause
you problems if you try to use it as a field (or calculated field) name.

 

[Steve]

I have to build a scheduling crosstab query with the
dates across the top, names on the left and data such as
vac(ation), sick, school, holiday...

-----Original Message-----
Why? For what purpose? Not being picky, but knowing the reason may mean a
better solution can be suggested.

I would suggest having a table with numbers from 1 to 13 (or even higher) in it
and then use that in a query.

Table: tblNumbers
Field: fldNumbers (unique with numbers from 1 to 13 or whatever limit you need)

Then to get the all the dates you need is a query like:

qry13Dates:
SELECT DateAdd("d",fldNumbers,Date()) as MyDates
FROM tblNumbers
WHERE fldNumbers <= 13
ORDER By DateAdd("d",fldNumbers,Date())

You can use this query whereever you would have used the table.

Note that my example used a name for the field that is not Date. Date is a
reserved word in Access (I used it to build the query) and will tend to cause
you problems if you try to use it as a field (or calculated field) name.

I want Access to create a table when the database opens.
The table will have one attribute [date] and include the
date the db is opened and the following 13 dates.

Any help is greatly appreciated.

.