Problem with using dates 3

[ThomasJF]

Hello!<br>I have run into a problem when trying to create a database in MS Access.<br>The database is a register for members in a organization.<br>When a person has been a member for 10 years , he or she gets a distinction.<br><br>My &quot;TBL_member&quot; table looks like this: (in addition to name, date of birth and so on)<br><br>one field that stores when the member enrolled (date - 00.00.0000)<br>one field for &quot;10 years distinction&quot; (date - 00.00.0000)<br><br>What I am trying to do is:<br>When a person has been a member for ten years, the date for the distinction (10 years from the date he/she enrolled) automatically appears in the &quot;10 years distinction&quot; field.<br><br>Can anybody tell me how to do this? And, by the way, I am not experienced in programming or making macros.<br>Thank you for any help!<br><br>Best Regards,<br>Thomas Fyhn<br><A HREF="mailto:thomasjf@online.no">thomasjf@online.no</A><br><br><br><br><br><br><br><br>

 

[Cornerstone]

Well if you do not want to get into macros or code then you can manually run a query everyday. <br><br>Create a new query. Make it an update query. Add the 10 year distinction field to the query grid. Set the Update To row to date().&nbsp;&nbsp;Set the criteria for the 10 year distinction field to Is Null.&nbsp;&nbsp;Add a new field in the field row, call it <br>LengthofService:datediff(&quot;yyyy&quot;,[enrolleddate],date())<br>Set the criteria to 10. <br><br>Be sure to make a backup of your register table first before running this. Run the Query and save it for daily use.<br>

 

[ThomasJF]

Ok, thank you for the answer. It worked. Sort of.<br>The only problem is that it deleeted all the posts that <br>not yet has got a distinction. Can this be done so that only the &quot;distinction&quot; field becomes blank?<br><br>Best Regards,<br>Thomas Fyhn<br>

 

[Cornerstone]

First if anything was deleted make sure you did not create a delete query. <br><br>The only other thing I can imagine you might have done was type Is Null for the Update To rather than the Criteria for the 10 year distinction field. Check that.<br><br>

 

[ThomasJF]

No, I did it exactly as you told me, but it still deletes...strange.<br><br>But I wonder if this is a method I can use:<br>In my member query i added the following:<br>10-year-distinction: DateAdd(&quot;yyyy&quot;;10;[TBL_member].[enrolled])<br>Then I have the date, but how can I hide the fields that<br>have a &quot;10-year&quot; date ahead in time? I would tip that I would have to write something in the criteria field?<br>Am I onto something here? Maby not <br><br>By the way, if I started to experiment with modules, would that be a better solution?<br><br>Best regards,<br>Thomas Fyhn

 

[elizabeth]

FYI, normally one does not store a calculated field in a table at all. You do the calculation only in (select) queries, reports and forms. If you need to do the same calc repeatedly, you would put it in a module and call or refer to it in your other objects. The reason you normally don't store calculations is that by nature they change. For instance, someone might have the enrolled date miskeyed. If the date is corrected, you have no way of insuring (at the table level) that the &quot;distinction&quot; date is changed correspondingly. This causes a lack of data integrity in your database.