By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,266 Members | 1,816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,266 IT Pros & Developers. It's quick & easy.

Auto populate date field based on another date field

P: 37
I want my "requalify date" to auto populate to the beginning of the month in the following year when my "liability date" gets entered/changed.

Example: Liablity date = 11/15/2010; 7/31/2011

Requalify date should be 11/1/2011; 7/1/2012

I am not sure how to write the formula to get this result.

The requalify date is a field in my main table as well as the liability date. When entering the formula, would entering it in the requalify date field's default value be the best.

Any help would be much appreciated. Thank you!

Yappy
Aug 3 '10 #1

✓ answered by Steven Kogan

It could go in a query as a calculated field, or as the control source on the form.

Using it as a default value wouldn't work, as you've found out.

Did you want the value to be editable, or always based on a calculation? If you want it to be initially calculated after the liability date is entered, and then to be able to edit it after, you'd need to use VBA.

The code would be something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Liability_Date_AfterUpdate()
  2.     If IsDate([Liability Date]) Then
  3.         [Requalify Date] = DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
  4.     End If
  5. End Sub
  6.  

Share this Question
Share on Google+
9 Replies


Expert 100+
P: 107
Try using DateSerial(year, month, day) combined with DatePart(interval, date) to build your formula. The formula for requalify date can be:

DateSerial(DatePart("y",[Liability Date])+1,DatePart("m",[Liability Date]),1))
Aug 3 '10 #2

Expert 100+
P: 107
Sorry, that should be:
DateSerial(DatePart("yyyy",[Liability Date])+1,DatePart("m",[Liability Date]),1)
Aug 3 '10 #3

P: 37
Hi, Steven!
Thanks for your reply. I do have another question since I am not sure if I am placing the expression in the correct area.

When entering the formula, would entering it in the requalify date field's default value be the best?

I have tried that and am getting errors.
Aug 4 '10 #4

Expert 100+
P: 107
It could go in a query as a calculated field, or as the control source on the form.

Using it as a default value wouldn't work, as you've found out.

Did you want the value to be editable, or always based on a calculation? If you want it to be initially calculated after the liability date is entered, and then to be able to edit it after, you'd need to use VBA.

The code would be something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Liability_Date_AfterUpdate()
  2.     If IsDate([Liability Date]) Then
  3.         [Requalify Date] = DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
  4.     End If
  5. End Sub
  6.  
Aug 4 '10 #5

NeoPa
Expert Mod 15k+
P: 31,660
You will find it complicated to keep updating the value. Not impossible, but complicated.

This is not something you should really even be considering (See Normalisation and Table structures). A normalised database would calculate the related value as and when needed, but never store a value that can always be calculated reliably.
Aug 4 '10 #6

Expert 100+
P: 107
I agree: if the value can always be calculated then it should not be a field in your table. If that's the case, add Requalify Date as a calculated field in a query. The exception would be if the database is for Access 2010, which supports calculated fields in tables.

The field in the query would be something like:

Expand|Select|Wrap|Line Numbers
  1. Requalify Date: DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
Aug 4 '10 #7

P: 37
Thank you so much! That is exactly what I needed. I tested it and it works perfectly.
Aug 4 '10 #8

NeoPa
Expert Mod 15k+
P: 31,660
Steven,

you may want to check your PMs :)
Aug 4 '10 #9

Expert 100+
P: 107
Hi NeoPa,

I'll be sure to include code in the [code] tags.

I'm unable to reply (regarding changing my status to Expert) with a PM. I haven't sent any, but I get the message:
Registered members under 50 posts can only send 3 private messages a day.
Aug 4 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.