Default value expression help

[jonman03]

Hello all,

I have 2 date fields [Date Received] and [Actual Due Date] in my table.

A user types a date (mm/dd/yyyy format) in [Date Received] and I want the [Actual Due Date] to auto-populate based on this value.

For example: "3/13/2010" is typed in [Date Received], I want [Actual Due Date] to say "3/30/2010".
If "3/17/2010" is typed in [Date Received], I want [Actual Due Date] to bump to the next month and say "4/30/2010".

Long story short, if a [Date Received] is after the 15th of the month, its actual due date is the 30th of the following month. If it is received on or before the 15th, its actual due date is the 30th of the same month.

I assume there is a way to due this with the expression builder default value, but I don't know the syntax.

Thank you for the help!

 

[SOS]

You don't store this information in the table. That violates the rules of normalization. You would use a QUERY to DISPLAY that date but you do not store it. You can use the DateAdd feature along with an IIF to get the appropriate date in a query.

 

[jonman03]

I understand.

How can I create this to display in a query? I'm not familiar with the DateAdd or IFF functions. I need some way to separate records that need to be addressed by this month end, or next month end.

Thank you for the quick reply.

 

[SOS]

In a query you can create a new field like this:

Actual Due Date: IIf(Month([Date Received])<>12,IIf(Day([Date Received])>15,DateSerial(Year([Date Received]),Month([Date Received])+2,0),DateSerial(Year([Date Received]),Month([Date Received])+1,0)),IIf(Day([Date Received])>15,DateSerial(Year([Date Received]),Month([Date Received])+1,0),DateSerial(Year([Date Received]),Month([Date Received])+2,0)))

 

[jonman03]

Thank you very much! Works perfectly.

 

[SOS]

Glad we could help. It was a bit of a long one, so I feel better that I did it for you rather than make you come up with that one.