Hi All,

I have a table that I used to pivot from in other queries. There are only two fields, the ID field and the Date field.

When I update the database I want it to populate the date field sequentialy from date()-10 to date()+500 with each date in its own record. I would prefer to do this with SQL, but if there is an easy way to do it with VBA I am willing to learn that. I am building this database for someone else and in a years time the dates will need to be updated so I would prefer to automate this process.

01/01/2012
01/02/2012
01/03/2012
etc.

Any Help would be apprecaited.

Thanks to All!!!!

Hi,



What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I would suggest that you modify your table to have only one field with numbers from -10 to +500 (including 0). Then, you can just use a query to get the dates, and you can use this query in place of your current table for your pivot process.

The query SQL might look something like:

SELECT DateAdd("d", [FieldName], Date()) As MyDate FROM TableName

Just my 2 cents...

I'll agree with the DB guy (heck, I pretty much always agree with the DB guy)... it's very handy to have a little multipurpose utility table. Mine is called Num, with one Long Integer field N, with values from 0 through 10000. To get a series of dates starting at the first day of the current year for 500 days, you can just use a query like

SELECT DateSerial(Year(Date()), 1, N+1) FROM Num WHERE N < 500;

and use this query anywhere you'ld use the dates table. And since it uses the Date() function to get its starting point, it will never go stale!

I ended up having a table with two fields. The first called number, populated 1-500, the second called Date.

I then run an update query for the Date field to equal Date()+[table].[number]

sometimes the simplest things are the hardest to see.

Hi,


Glad to hear you found a solution that works for you; but, I believe you missed the point of our replies. When you use an UPDATE query and store the dates in your table, then you end up with a "static" value (until you run the update query again. Whereas, what we have given you is more of a "dynamic" way of generating dates for your needs.

Good luck with your project.