Calculate a field value based on another field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a table that contains a date (that the user can modify or leave as
default of today). That same record has another date field (expiration date)
that is calculated based on the value of the original date field.

Access won't allow me to set the default value of the expiration date within
the table definition by using the value of another field. So, I've got it
defaulting to calculating based on Now().

So, I'm trying to do it in code. I've tried AfterUpdate and OnChange, but
it isn't working. Not getting an error, it just isn't changing.

TIA
 
I've got a table that contains a date (that the user can modify or leave as
default of today). That same record has another date field (expiration date)
that is calculated based on the value of the original date field.

Access won't allow me to set the default value of the expiration date within
the table definition by using the value of another field. So, I've got it
defaulting to calculating based on Now().

So, I'm trying to do it in code. I've tried AfterUpdate and OnChange, but
it isn't working. Not getting an error, it just isn't changing.

TIA
Click to expand...

You don't need to store the calculated expiration date value.
As long as you have the Original Date stored, any time you need the
expiration date, calculated it. That's the correct way to use Access.

In the control source of an unbound control on a form or in a report:

=DateAdd("d",90,[OriginalDate])

will add 90 days to the Original Date.
But do not store this value!
 
You are SO right! Thank you for that insight!

fredg said:
I've got a table that contains a date (that the user can modify or leave as
default of today). That same record has another date field (expiration date)
that is calculated based on the value of the original date field.

Access won't allow me to set the default value of the expiration date within
the table definition by using the value of another field. So, I've got it
defaulting to calculating based on Now().

So, I'm trying to do it in code. I've tried AfterUpdate and OnChange, but
it isn't working. Not getting an error, it just isn't changing.

TIA
Click to expand...

You don't need to store the calculated expiration date value.
As long as you have the Original Date stored, any time you need the
expiration date, calculated it. That's the correct way to use Access.

In the control source of an unbound control on a form or in a report:

=DateAdd("d",90,[OriginalDate])

will add 90 days to the Original Date.
But do not store this value!
Click to expand...
 
Hi guys,
while I agree with not storing anything that can be calculated on the fly, where do you draw the line with how easily the new value can be calculated?
I see the simple "add 90 days" which makes sense but what about having to calculate using another stored value in a different table.
Like a customer leadtime that is specific to that customer that needs to be looked up instead of the "90" days.
Should the expression just get more complex? (or use another stored query)
I'm having difficulty finding the right approach (and I'm sure I'm missing something stupid) to get this to work.
My app is an Orders table that has Ship_date trying to get calculated from the entered Required_date using Leadtime from the Customers table. Sounds simple but I can't describe it well enough to search for more help.
What would your approach be?
 
Back
Top