Access MS-ACCESS 2010 - Date Stamps

[ProRod21]

Hello,

1st Question:

I'm currently building a Database for my job. I have a field titled "Date Received." What I want to have access do is automatically populate a "Due Date" for 2 business days after the "Date Received" field.


2nd Question:

In my database there is a "Status" field where the agents will be able to select a status from a drop down menu. Is there a way to keep record (auto Date/Time stamp) every time the status is changed? How do I do this?

I have this in a Form. I'm fairly new to ACCESS but am a quick learner. Please help me with this!!!!

Thank you for any help! Just joined this site today so I hope I can find the help I so need!

 

[ProRod21]

I need help! Some one please help me with this!

 

[floppybootstomp]

I need help! Some one please help me with this!

If nobody's answered, probs means nobody knows a solution. Patience!

Good luck in solving your problem.

 

[DSG]

(now)+2 should work

 

[alow]

I've been slacking in the forums here lately . If you want the field to always show 2 days after today's date, then the formula that DSG gave you should work. If you're looking for a date that is 2 days after the Date Received field, you could try the following formula:
=DateAdd ( "d" , 2 , [Date Received]).

Good Luck!

 

[ProRod21]

I've been slacking in the forums here lately . If you want the field to always show 2 days after today's date, then the formula that DSG gave you should work. If you're looking for a date that is 2 days after the Date Received field, you could try the following formula:
=DateAdd ( "d" , 2 , [Date Received]).

Good Luck!

alow,

Thank you for your help! I'm sorry where should I plug this formula into? Is this done through the property sheet (if so which field) or through the main table where the field (Date Received/ Due Date) is located?

 

[alow]

To create a calculated field on a form, insert a text box and put the formula in the Control Source property.

 

[ProRod21]

Alow,

Thank you so much it works!

Question, how do I get this now to reflect back to original table? I want it to populate the due date in the database table.

Also, is there a way to send an alert (via email - outlook or even just through the database?) I don't know how this works, your help is highly appreciated!

 

[alow]

If you want it to append those values back to the original table, it will require either code, or an update query attached to code, or something that I'm not familiar with, honestly. I could walk you through creating an update query that will do it in a one-off and then you don't need the calculated field in the form. But going backwards from the form to the table with a calculated field is tricky.

What kind of alert are you looking for? (I have no idea how to integrate Outlook with Access, but the answer to this question might help someone else answer it.)

It's a mouthful, try to wade through it and let me know if it helps.

 

[ProRod21]

Yes please tell me how the you would create an update query that will do it in a one-off. If you wouldn't mind can you please explain how this would be more beneficial or even just how this would help (I want to explain it back to my peer). It doesn't need to be through Outlook. The Alert is fine just through Access. OR do you believe a report would just be more beneficial?

okay, this is now going to get a bit more challenging, I have 4 Categories that the agents can select that have 4 different future due dates.

For example

Appeal - 45 Days
Expedited Appeal 3 Days
Grievance- 90 Days

Is there any possible way to tie in the Category Chosen (Appeal, Expedited Appeal, or Grievance) to the number of days the "Due Date" would auto populate?

Sorry to be annoying! I'm currently taking a class on MS Access but we haven't come to this part yet and I would like to have this for my boss.

Thank you!

 

[ProRod21]

Also! Is there any way to only count business days?! (Monday-Friday)

 

[alow]

I haven't forgotten about you. I'll need to find a little bit of free time to get you a proper answer. Maybe someone else will step in before then, and you can always try a little bit of Googling.

 

[ProRod21]

Thank you Alow! I've been searching through google for the answer... I've searched several pages but if I do come across anything I will be sure to let you know!

Honestly thank you for all of your help and anything else you're able to help me with is greatly appreciated!

 

[alow]

I haven't had a chance to really dive into this for you, but this link might hold you over. It's the basics of creating an update query. As far as only counting work days between dates, there isn't an easy answer in Access. In Excel, you would use the NETWORKDAYS function, however to recreate it in Access you would need VBA, and this one is above me. You can try this link to see if you can get that working.