Auto populate date field based on another date field

[yappy77]

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

[Steven Kogan]

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))

[Steven Kogan]

Sorry, that should be:
DateSerial(DatePart("yyyy",[Liability Date])+1,DatePart("m",[Liability Date]),1)

[yappy77]

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.

[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:

Private Sub Liability_Date_AfterUpdate()
    If IsDate([Liability Date]) Then
        [Requalify Date] = DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)
    End If
End Sub
 

[NeoPa]

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.

[Steven Kogan]

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:

Requalify Date: DateSerial(DatePart("yyyy", [Liability Date]) + 1, DatePart("m", [Liability Date]), 1)

[yappy77]

Thank you so much! That is exactly what I needed. I tested it and it works perfectly.

[NeoPa]

Steven,

you may want to check your PMs :)

[Steven Kogan]

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.