Auto calculate a date field based on another?

[Mytitan]

Hellow all,
I'm working on a database and trying to go above my skill level, regarding coding.
In my Form I have one field titled “Audit Due (9 months)” and is a date format.
I have a field before this one titled “Sub-recipient’s Fiscal Yr End”, which is also a date format.

What I’m trying to do is have the “Audit Due…” field automatically populate with the date in the “Sub-recipient’s…” but adding 9 months to that date.

For example, if 01/01/2013 is entered in “Sub-recipient’s Fiscal Yr End” field then I want the “Audit Due (9 months) field to be auto-filled with 10/01/2013.


The closest I’ve been able to find is the following:
=DateSerial(Year([Audit Due (9 months)]),Month([Audit Due (9 months)]),Day([Audt Due (9 months)])+9)
But this hasn’t worked so far.
If I can get this to work in the Form I'd like to see what I can do in the table.
Thanks for any and all help!

 

[billmeye]

Use:

Me.Audit_Due_(9_months) = DATEADD("m",9,Me.Sub-recipient’s_Fiscal_Yr_End)

 

[Mytitan]

Thanks billmeye
I entered the code:
Me.Audit_Due_(9_months) = DATEADD("m",9,Me.Sub-recipient’s_Fiscal_Yr_End)

Here is what the screen showed afterwards:
Private Sub Audit_Due__9_months__AfterUpdate()
Me.Audit_Due_(9_months) = DATEADD("m",9,Me.Sub-recipient's_Fiscal_Yr_End)
End Sub

It gave me an error pointing to the underscore after the "9". I tried taking the underscore off but then the code doesn't take. ?

 

[billmeye]

You need to make sure the reference to your controls is correct.

Me.Audit_Due__9__months = DATEADD("m",9,Me.Sub-recipient’s_Fiscal_Yr_End)

The real trouble is your naming convention. You are using spaces and special characters which is a bad idea as it creates problems for Access hence all the underscores replacing the spaces and special characters.

 

[Mytitan]

Ok, that did the trick, thanks! I'll work on the naming of the fields, to prevent the underscores and ().

Now if I wanted to have this same code set up in the table of the database, would the set up be any different? I'm not sure where I would start at in the design view of the table?

 

[billmeye]

Well, you'll find all the big boys don't use calculations in the table, only in the form or query or report so I'd stay away from that. If you are trying to update existing entries in your table, that can be done with an Update query.

 

[Mytitan]

I've had someone tell me before not to add it to the table but I wasn't sure. But if you're telling me it's a bad idea then I'm more inclined to listen to your advice!
Thanks for the help on this, it helped tremendously. I know I'll have more inquiries so it's great that this site exists!
Thanks again!

 

[Mytitan]

I've one more inquiry regarding the same database. I'm not sure if I need to just reply to you or start a new thread? I'll go ahead and send as a reply but let me know if it's better to start a new thread for this:

In my database I have the following fields (or text boxes) in my Form:

Field 1 is labeled: Review Status
Field 2 is labeled: Date Audit Report Received
Field 3 is labeled: CQC Audit Review Due Date

The data in Field 1 is limited to: “Review Complete”, “No Review Needed”, or “In Process Review”.

The data in Field 2 is limited to: “Need”, “an actual date, such as 02/15/2013”, “Unknown”, or “N/A”.

The data in Field 3 is limited to: “Complete”, “Need”, or “6 months + date in field 2”.


Here’s what I’m trying to accomplish in the Form:

1. If Review Status shows “Review Complete” or “No Review Needed” then I want the CQC Audit Review Due Date field to show “Complete”.

2. If Date Audit Report Received shows “Need” then I want CQC Audit Review Due Date to show “Need”.

3. If the Date Audit Report Received shows a date then I want CQC Audit Review Due Date to show that date + 6 months.

I just can't seem to get too far past the VBA coding on these. I get as far as the Me.????? but just get stuck at that point.
Thanks again for any and all help!

 

[billmeye]

Although you can go ahead and store a date in a text field (it will be entered as if it were text) and convert it to a date for calculations, you will run into errors when it doesn't quite match up. It is usually best to have a devoted date field and a devoted text field and work them together to accomplish your goal. Plus, you may want to know completion dates for future reference but you won't have them with your scenario. With that being said, you can work with what your doing for now.

1. If Review Status shows “Review Complete” or “No Review Needed” then I want the CQC Audit Review Due Date field to show “Complete”.

In the After Update event for Review Status, add the code:

If Me.Review_Status = "Review Complete" Or Me.Review_Status = "No Review Needed" Then Me.CQC_ Audit_Review_Due Date = "Complete"

2. If Date Audit Report Received shows “Need” then I want CQC Audit Review Due Date to show “Need”.

You'll notice a trend, in the After Update event for Date Audit Report Received, add the code:

If Me.Date_Audit_Report_Received = "Need" Then Me.CQC_ Audit_Review_Due Date = "Need"

3. If the Date Audit Report Received shows a date then I want CQC Audit Review Due Date to show that date + 6 months.

This is where you could get errors but I believe IsDate should prevent that from happening. Add this code in the After Update:

If IsDate(Me.Date_Audit_Report_Received) Then Me.CQC_ Audit_Review_Due Date = DateAdd("m",6,CDate(Me.Date_Audit_Report_Received))

For future question please start a new thread, you'll get better responses.

 

[Mytitan]

I agree, I should have had a separate field just for dates, rather than lumping that into a text box.

It looks like the above coding is working smoothly. I'll be adding more data so I'll be able to test it further, just to be safe.

Is there an actual manual or any type of quick reference guide for learing the VBA coding? I'm still getting my feet wet with it but seems like I'll never be going any deeper!

Thanks again for your help, it is greatly appreciated!