[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

How to allow edits to a calculated date field on a form

"Expiration Date" is this calculated field on a query: ExpirationDate: DateAdd("yyyy",5,[EffDate]). This query is the record source for my data entry form.  There are a few situations where the value calculated for this field needs to be changed.  Do I need to create a separate, "Revised Expiration Date" field for the corrected date, or direct user to enter the date in a comment field; or is there a way to allow user to change the date in the calculated field (preferred, if an option)?
0
Tucker22
Asked:
Tucker22
  • 4
  • 2
  • 2
  • +2
4 Solutions
 
danishaniCommented:
Good question, while you can calculate the Expiration Date on demand, you don't have to store this.
Now, when you want the user to enter an alternative date, which differs from the calculated standard expiration date, situation is little different.
You still can calculate this on demand, however something needs to be triggered to calculate different value from standard. The question then is this only in years or will it be even months?

If the expiration date only differs in years, then you can store this numbe in your table. For example if product x have 5 years lifetime, you store the number 5 for this product in the field LifeTime. Is it 6 years, 6. Your calculated expiration date will then be;
ExpirationDate: DateAdd("yyyy",[LifeTime],[EffDate])

When this will be in months or even a complete different date value, you might consider to store the Expiration Date in your table.

HTH,
Daniel
0
 
Tucker22Author Commented:
This is helpful!...but, how do I cause the calculated field to be stored in the table? Can you please help me with this?  I was surprised to see that it appears on the form, but does not automatically enter to the table.  Thanks.
0
 
Tucker22Author Commented:
p.s.  I'd like for the calculated field to update the table, and to also have the option to revise it on the table, although this need to revise would be infrequent.  
By the way, I am not familiar with the "on demand" criteria that you refer to.  Thanks again.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Gustav BrockCIOCommented:
> I was surprised to see that it appears on the form, but
> does not automatically enter to the table.

Really? Where should it "enter to the table"?

1. Add the Date field, ExpirationDate, to the table having a default value of Null
2. Bind your textbox, txtExpirationDate, to this field
3. Add code in your form in the OnCurrent event:

  If IsNull(Me!txtExpirationDate.Value) Then
    Me!txtExpirationDate.Value = DateAdd("yyyy", [LifeTime], [EffDate])
  End If

/gustav
0
 
GRayLCommented:
You could make the Default Value the calculated value which can then be overwritten at any time by the user.  Note that Default Value applies only to fields in new records - at which time it can be accepted by doing nothing, or altering it as the user sees fit.
0
 
danishaniCommented:
Why to store the Expiration Date, when you can calculate it on Demand?
I rather not store calculated values, other then I have no choice.

The term "On Demand" is when you need a calculation, you can trigger this by create a calculated field as you did in a Query/Form/Report.

HTH,
Daniel
0
 
Jeffrey CoachmanCommented:
<There are a few situations where the value calculated for this field needs to be changed.>

Then change what gets fed into the calculation.
If a field is calculated, you cannot simply "Change" the result.

That would be like saying 2+3=5
...But, "There are a few situations where" 2+3 "needs to be changed" to equal 47

Instead, you should simply change the value of Two (2) to 44
Make sense?

;-)

JeffCoachman
0
 
Tucker22Author Commented:
Thank you to Danishani, Gustav, GRayL and boag2000!  All of your comments were very helpful.  The additional piece of information that i needed was how to create a macro using an appropriate SET VALUE action; a co-worker helped me with this step. I appreciate all of your help!
0
 
Jeffrey CoachmanCommented:
ok
0
 
Tucker22Author Commented:
To gustav: I and the other users in my work unit don't know Visual Basic; that's why I used an embedded macro--instead of the coding that you provided--on the form in the oncurrent event property.
0
 
Gustav BrockCIOCommented:
OK. Thanks for the feedback!

/gustav
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now