Access Table Defualt value based on another field in same table

Question

Hi All,

I have 3 fields in access table:

1. Today's Date- defalut to = date() which also do not work. It doesn't update all the records
   
2. Upload Date- Is a query which inserts date() function when data is imported. works well
   
3. Payment Date

In payment date, i try to set default value as =[Today's Date]-[Upload Date]

I get error saying don't have field Today's Date in my table.

Thanks,

Zav

Answer 1

Are you getting the message, "The database engine does not recognize either the field 'Today's Date' in a validation expression, or the default value in the table '<your table name>'"?  If so, that's not the same as saying you don't have the field in your table.

The help topic for Field.DefaultValue property (in the DAO reference) says, "If the property setting is an expression, it can't contain user-defined functions, Microsoft Access database engine SQL aggregate functions, or references to queries, forms, or other Field objects." I don't think you can set the default value to an expression referring to another field.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 2

>>>I get error saying don't have field Today's Date in my table. 

According to your description, I have reproduced this issue, this issue is caused by that a field's default value is applied at the instant a new record is created. This workaround is that you could use a data macro as a pseudo-trigger to accomplish what you want.

Thanks for your understanding.