AUTODATE

[Nixxie]

I need to put a formula into my database that automatically calculates a date.
ie: I have a 'start date' field in my sheet and and a 'target reply' field
which needs to be 28 days later than the 'start date' .

Is there some formula or expression that will calculate this to show in the
table rather than just in a query?

(I know it seems lazy to not add by hand this but the database has several
inputters and things can go wrong.)

Any help would be gratefully received.

Thanks

 

[Cyberwolf]

Usually you would not want to store a calculated field in your table. But
you can do something like this:

Add a field into your table called somehting like this "TargetReply" and set
the field type to Date/Time data type.

Now open the form attached to this table in design view and create a new
field that links the the TargetReply field. Then on the AfterUpdate event on
the StartDAte field put in the following code

me.TargetReply = me.StartDate + 28

THis is untested code and you may need to tweak it

HTH

 

[Nixxie]

Thanks,

I will try that, Can you just give me a little advice in regard to
'AfterUpdate' as I have never heard of it! I am gradually teaching myself
about Access through this forum and havent come across this one before!

 

[Cyberwolf]

To get to the after update Right click on the StartDate field than click on
properties. At the top of the Properties box is 5 tabs. One is labeled
events. CLick on the tab. there will be an "After Update" Put your cursor
in this field and on the right side there will appear 2 boxes, one with 3
dots "...", and another with an arrow pointing down. Click on the one with
the 3 dots and the choose the Code Builder option and click OK. This will
open up the Microsoft Visual Basic window. Once this is open just type in my
code below that I provide earlier. Make sure the the the name of the
TargetReply is labeled as "TArgetReply" otherwise the code will not work. To
check this right click on the TargetReply field and click properties and then
click on the Other tab at the top and look at the "Name" property.

HTH

 

[Nixxie]

It seems to not be working which is not unusual for my programming and I must
be typing something wrong.

Me.Target_Reply_Date = Me.Start_Date + 28 is what happened when it offered
me the drop down boxes, I have put this in the Start Date AfterUpdate???

Apologies for seeming dim its getting dark here and my understanding is
waining.

Thanks Again

 

[Cyberwolf]

It looks like the syntax is correct. Are you getting an error message, or is
it just not populating the Target_Reply_Date field?

Here is my code

Private Sub Start_Date_AfterUpdate()

Me.Target_Reply_Date = Me.Start_Date + 28

End Sub

If the StartDAte field is already populated, you will want to run an update
query. Do the following. Create a new query in design view add the table
that contains the 2 fields. Then add the Start_Date field and the
Target_Reply_Date field now change the Query type to Update Query. To do
this click on "Query" in the menu at the top and click on Update Then in the
"Update To" field below Target_Reply_Date Type [Table_Name].[Start_Date] + 28
Where Table_NAme is the name of your table. Then in the Criteria field below
the Start_Date type "Is Not Null" This will fill all of the field
Target_Reply_Date fields where the Start_Date field are not blank. Please
note In the above example it may be necessary to replace the underscores (_)
with spaces, ie if the actual name of the fields are Start Date and Target
Reply Date You would not use the underscores. If you are still having
problems email me @ cyberwolfent @ hotmail . com minus any spaces and we can
continue this that way. This will allow us to pass date more easily.