locked
Access Table Defualt value based on another field in same table RRS feed

  • 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


    • Edited by zaveri cc Thursday, October 13, 2016 3:57 PM
    Thursday, October 13, 2016 3:57 PM

Answers

  • >>>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.

    • Marked as answer by zaveri cc Friday, October 14, 2016 3:58 PM
    Friday, October 14, 2016 5:57 AM

All replies

  • 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

    Thursday, October 13, 2016 10:02 PM
  • >>>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.

    • Marked as answer by zaveri cc Friday, October 14, 2016 3:58 PM
    Friday, October 14, 2016 5:57 AM