Expert: Richard Rost - 10/20/2009

Question
QUESTION: Hi

I’m a beginner with Access.
Basically I need to add a control in my form which will show the days elapsed between two dates.
Many thanks for your help.
Raz


ANSWER: That's a simple calculated field.

If you have D1 and D2 and want the difference between them, make a new UNBOUND Text Box (not tied to a table field) and set the Control Source to:

=D2-D1

Or, you could also do this as a calculated query field if you need to have this value anywhere else (in reports, for example). See:

It sounds to me like you just need to learn how to properly create CALCULATED QUERY FIELDS. You can make a query that will perform math operations for you automatically.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to create calculated query fields:

http://599cd.com/tips/access/calculated-query-fields?key=AllExperts

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT ACCESS VIDEO TUTORIAL. Go to http://www.599cd.com/Access/AllExperts


Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Access Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

http://www.599cd.com/Microsoft_Access_Tutorial?key=AllExperts

I hope this information helps to solve your problem. If there is ANY REASON why you won't give me a RATING of 10 please let me know so I can improve upon my answers in the future. Thank you!

Sign up for my FREE NEWSLETTER with lots of TIPS and TRICKS. Visit http://www.599cd.com/MailingList?key=AllExperts

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts

---------- FOLLOW-UP ----------

QUESTION: Hi Richard,

Thank you for the solution but I think I should have been a little bit more precise in what I need to achieve. Basically I need the Date Today field (see attached picture) to update automatically everyday. From the Date Today field, I need to show the days elapsed in the Days Elap from Initial Submission field. I guess the formula would be

Date Today – Date Received = Days Elap from Initial Submission

The problem is how and where do I do the formula and how do I show it on the form.

Many thanks once again.


Answer
It's pretty much the same thing. You don't really even need the "Date Today" field. You can do it by setting the control source in the DaysElapsed field to:

=Date()-DateReceived

Let me know if you have any other questions or comments.

Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT ACCESS VIDEO TUTORIAL. Go to http://www.599cd.com/Access/AllExperts


Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Also, don't forget to visit my FREE Microsoft Access Tips & Tricks Page at http://www.599cd.com/Tips/AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

http://www.599cd.com/Microsoft_Access_Tutorial?key=AllExperts

I hope this information helps to solve your problem. If there is ANY REASON why you won't give me a RATING of 10 please let me know so I can improve upon my answers in the future. Thank you!

Sign up for my FREE NEWSLETTER with lots of TIPS and TRICKS. Visit http://www.599cd.com/MailingList?key=AllExperts

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts