auto dates and date add in table

[listgirl3]

I'm putting a very basic table (form on the front end) together to collect data, and I want the date field to be populated with the date the data is entered, and I don't want it to change - will =date() or =now() do that?

Also, I want to calculate a DateAdd on a date field that is manually added and will be variale, and I want it to be a year from that date...so I get that part (=DateAdd('yyyy',1) - but I don't know how to specify which field to DateAdd from....

Any help please?

 

[plog]

Set the default value on the table to =Date() to get it to automatically put the current date when a new record is created.

For the DateAdd function (http://www.techonthenet.com/access/functions/date/dateadd.php) the date to add to is the last argument so this is what you should have:

=DateAdd("yyyy", 1, [YourDateFieldNameHere])

 

[listgirl3]

Thank you!!

 

[listgirl3]

Still need help...that did not work. It's telling me it can't recognize the field name in the validation expression (?). Not sure where I'm going wrong here...

 

[plog]

1. Where did you put this? Query, Form or report?

2. What's the name of the Query or Table that is feeding the answer to #1 above?

3. Show the exact code you used in #1 above and then sample data from #2 above.

 

[Pat Hartman]

If the calculated date is always one year from the fixed date, there is no point in storing it. You should calculate it in a query.

 

[listgirl3]

To Pat - the one year calculated date is from a variable date...which is why I want it - it's for document retention policy monitoring.

Plog -

1. I've put the query in the form.
2. HCInternalRequset is the table
3. =DateAdd('yyyy',1,[HDRneed]) HDRneed being the date the data is needed, driving the one year end date - it is a shortdate field
sample data - 05/13/2011

There is more information on the form but it does not relate to these fields (text fields for names, etc.) There is not much data being collected for storage.

 

[Pat Hartman]

In your first post, you said the date wasn't variable. Once the date added is entered, it can't be changed. Therefore, you don't need to store the calculated date. It is always 1 year from the date the document was added.

Select fld1, fld2, fld3,..., DateAdd('yyyy',1,[DateAdded]) As HDRneed
From ...

 

[listgirl3]

I finally got this to work in my form - but on the text box is unbound, and I would really like to send the DateAdd result to my table...(I know, calculation not always the best info to sent to the table), but it is relevant. How do do that if my text box is unbound?

 

[Pat Hartman]

I know, calculation not always the best info to sent to the table
In a properly defined table, they are NEVER stored. What is your aversion to doing this the right way? It is reasonable to store calculated values that represent some value at a point in time but since the base date can't change, then the calculated result can't change.
All of your forms and reports should use queries as their RecordSources so having the calculation in a query shouldn't be burdonsome.

 

[Beetle]

You have said that you do not want the users to be able to change the Entry Date, but what is still unclear is whether or not you want them to be able to override HDRneed value if necessary. If not, then you should not store the value in a table, just calculate it as you are now. If you do want them to have the option to change it, then it would be appropriate to store it.

If the latter, see this link