469,338 Members | 8,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

Auto populate date field based on another date field

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.  

9 8434
Steven Kogan
107 Expert 100+
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
Steven Kogan
107 Expert 100+
Sorry, that should be:
DateSerial(DatePart("yyyy",[Liability Date])+1,DatePart("m",[Liability Date]),1)
Aug 3 '10 #3
yappy77
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
Steven Kogan
107 Expert 100+
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
32,182 Expert Mod 16PB
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
Steven Kogan
107 Expert 100+
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
yappy77
37
Thank you so much! That is exactly what I needed. I tested it and it works perfectly.
Aug 4 '10 #8
NeoPa
32,182 Expert Mod 16PB
Steven,

you may want to check your PMs :)
Aug 4 '10 #9
Steven Kogan
107 Expert 100+
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.

Similar topics

6 posts views Thread by jochen scheire | last post: by
13 posts views Thread by pkinville | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.