Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > DateAdd < Calculate Age | Search Button >
Back to DateAdd    Comments List
DateAdd in Calculated Fields Upload Images   Link   Email  
Kent Jamison       
2 years ago
I've tried dozens of permutations, but nothing works.  Why can't you use DateAdd functions in a calculated field in a table?  It seems to work in a query, but not in the expression for a calculated field in a table.  Why is this?  Is this a known bug with Access 2016?
I'm just trying to understand why.  
P.S. I know how to make it work in queries, but this workaround adds a lot of extra effort.
Kevin Robertson            
2 years ago
Kevin Yip        
2 years ago
It's not a bug.  Some functions are not allowed for one reason or another.  Date() and Now() are not allowed because they change values every second, and Access doesn't want that happen to tables that are supposed to store static data only.  In fact, calculated fields in a table are frowned upon, because they store expressions, not static data.  DateAdd() isn't allowed, I imagine, because it performs complicated calculations such as checking for leap year, checking for the correct end-of-month day in February, etc.  Complicated calculations that may be performed repeatedly may degrade database performance.
Kent Jamison       
2 years ago
Kevin and Kevin,
Thanks to you both for your input.  I watched the videos (reg & extended cuts) again on calculated fields!  I understood simple fields are sometimes used in tables (e.g., for concatenating names).  My error was thinking DateAdd was also simple.  Your notes explain how that is not the case.  I get it now.  Thanks for the input (and correction).  The Evil Access Stuff list is now printed sitting next to monitor. ;-D
Kent Jamison       
2 years ago
Follow-up question:
I have created (successfully) the following DateAdd query, as follow via Query Design to track when an employee's training expires.  Some courses expire at one year, others two years, still others three years, hence the need for the ExpirationInterval field.  
CourseExpDate: DateAdd("yyyy",[ExpirationInterval],[DateCompleted])
I want to save the result the expiration date in my table TrainingT in a field named CourseExpDate.  How do I tell the Query to save that calculated value, which I want to save, in the corresponding field in TrainingT?  
--Kent J.
Kent Jamison       
2 years ago

Kevin Robertson            
2 years ago
Kent Jamison       
2 years ago
Kevin R.,
Is there another way besides an Append Q?  I don't want to have to run an Append query each time I update each employee's record with each of the 40 plus courses I have to update.  I would be hundreds of Append queries, and a lot of work.  I want Access to automatically calculate the new date and save it in the CourseExpDate field in TrainingT when I enter the date the last course is completed.  My intent is to then run reports on who is delinquent in not taking their required training.
Scott Axton            
2 years ago
Have you watched the Many-to-Many example of tracking certifications?

Access Expert 15 gives a good example of tracking attendance.  You could modify that to do your certifications.
Kevin Yip        
2 years ago
Hi Kent, use update queries if you want to update the CourseExpDate field in existing records.  Yes, you do have to run this every time you want to make an update.  If you have 100 records in which you need to update CourseExpDate, you need to make 100 different update queries.  This is usually automated in VBA with some kind of loop.
Richard Rost            
2 years ago
It's funny that you said the Evil Access Stuff is printed and sitting next to the monitor. I have on my "to do" list to make a "cheat sheet" version of that as a PDF for people to print out. I'm waiting until I finish making all of the videos for each item on it. Then I'm going to make one video that goes over all of them (in brief). Then the PDF. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in DateAdd.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/19/2024 11:08:27 AM. PLT: 1s