Table Autofill, is it possible?

[cranebill]

Im looking to create a timeline based on particular dates. In Excel I am able to do this with a simple formula and format the cell to a date, for example A1 has the date 1-30-01 in it, in a2 i can write a1-7 and format the cell and it will produce 1-23-01. (I know it seems silly writing this simple formula here of all places but I am trying to be as descriptive as possible.) How can i get the tables in Access to do the same? Any help in this matter would be greatly appreciated.

 

[MaeJams]

You can write a query based on your table and wrap a formula around your date field. The formula to use is the DateAdd function. Use the following syntax:

DateAdd("d", -7, [DateField])

The "d" parameter tells it to use days.
The -7 is telling it to add negative 7 days.
The [DateField] is the name of your date field in your table.

Hope this helps.
Rich

 

[cranebill]

Im not exactly sure what you mean, i did try the dateadd function in a couple ways. The only way i could seem to get it to work was in a form, but i need to be able to update the table to have these dates contained in the table for queries that are to be built in the future. "You can write a query based on your table and wrap a formula around your date field." What do you mean wrap a formula around the date field? Do you mean of the table or of the query. Could you please explain a little further. ( I feel dumb at this point)

Thanx, Bill

 

[MaeJams]

When I said wrap the formula around your date field, I just meant your date field is one of the parameters in the formula. Here is what you do:

1. Create a new query.
2. Select the table that has the date field you want to use.
3. In the first "Field" in your query grid, type in the formula I gave you in my first reply.
4. Run your query and you'll see the new calculated value.

If you need to add this value to your table, you will need to add the field to your table first, then create an update query. If you need to do this, let me know and I'll walk you through it.

Rich

 

[cranebill]

Ok, got that to work, then i tried my luck at creating an update query, to do this( which is wrong for it didnt work) I ran a update query based on the query with the formula as instructed earlier, when it asked "Update to" I put in the table and field name. It did not work..... so ill keep playing with it till i hear from you. I appreciate all the help you are giving. Thanx again.

Bill

 

[MaeJams]

In an update query, the field to which you want to write the new calculated value should be selected in the "Field" drop down list. The formula I gave to you should be entered in the "Update To:" field.

Rich

 

[cranebill]

Thanks alot that worked like a charm. One other quick question. Is there a way that you can turn off the help screen saying you are about to modify records so that it just automatically updates without asking?

 

[MaeJams]

You can turn it off by running your query in a macro. Create a new macro and for the first line, select "Set Warnings" from the Action drop down list. In the lower half of the screen, under Action Arguments, the "Warnings On" field should show "NO".

On the second line, select "Open Query" from the Action drop down list and select your update query from the "Query Name" field under Action Arguments.

Then, for the third line, select "Set Warnings" again, but change the "Warnings On" field in Action Arguments to "YES".

Then, you can run the macro, and it will turn the warnings off before running your query, and turn them back on after it is done.

Rich