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,

(IMG:style_emoticons/default/welcome2UA.gif)

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... (IMG:style_emoticons/default/2cents.gif)

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.