Calculated Field

[Richie27]

Hi all,



I need to have a field which is calculated base on what is input in another field.

I know, I know normalisation and all that says it's a bad idea, but client wants it regardless.

What I basically have is three date fields called say DATE1, DATE2 and calculatedDATE.

If the user inputs DATE1 but leaves DATE2 blank, I need to add 8 years to DATE1 one and store that in calculatedDATE.

Similarly if the user inputs DATE2 but leaves DATE1 blank, I need to add 20 years to DATE2 and store that in calculatedDATE.

Any inspiration on this would be wonderful.

Thanks in advance,
Rich.

[orange]

Why do you have to store the "calculatedDate"?

You can use a query to determine the calculated date based on your criteria.

My test shows that a default date will be #12:00:00 AM# if you don't assign it a specific value.
So you create an expression with that in mind.

I did the following test and it seems to work.
Now if Date1 and Date2 are not both #12:00:00 AM#, I don't know what you want to do. I just print a string

Sub testF()
Dim date1 As Date
Dim date2 As Date
'date1 = #2/1/2000#
date2 = #7/30/1943#
Debug.Print IIf(date2 = #12:00:00 AM# And Not date1 = #12:00:00 AM#, DateAdd("yyyy", 8, date1),
IIf(date2 <> #12:00:00 AM# And date1 = #12:00:00 AM#, DateAdd("yyyy", 20, date2), "BothDate1 andDate2haveValuesCheck"))
End Sub

[Richie27]

Why do you have to store the "calculatedDate"?

You can use a query to determine the calculated date based on your criteria.

My test shows that a default date will be #12:00:00 AM# if you don't assign it a specific value.
So you create an expression with that in mind.

I did the following test and it seems to work.
Now if Date1 and Date2 are not both #12:00:00 AM#, I don't know what you want to do. I just print a string

Sub testF()
Dim date1 As Date
Dim date2 As Date
'date1 = #2/1/2000#
date2 = #7/30/1943#
Debug.Print IIf(date2 = #12:00:00 AM# And Not date1 = #12:00:00 AM#, DateAdd("yyyy", 8, date1),
IIf(date2 <> #12:00:00 AM# And date1 = #12:00:00 AM#, DateAdd("yyyy", 20, date2), "BothDate1 andDate2haveValuesCheck"))
End Sub

I need to store a calculatedDate because this relates to when a certain action will take place. That action is the destruction of files based on which date is entered. They don't want to have to do any mental arithmetic. They just want to put in date1 or date2 and have the system automatically fill in the calculatedDate 20 or 8 years from the first date.

Should I do this query thing you suggested in VBA? Sorry but my programming skills are pretty poor as yet and under big pressure..........

any help appreciated.........

[Richie27]

Tried this in my VBA - no joy ;-(

Private Sub txtLeavingDate_AfterUpdate()

Dim date1 As Date

date1 = forms!serviceUser!txtDeathDate
Me![su_file subform].Form.file_destruction_due_date.Value = "DateAdd("yyyy", 20, # date1 #)

End Sub

Is that totally rubbish?

[RayMilhon]

Why use 2 dates. Have 1 Date Field and 1 boolean field for the time frame then you could just say IIF(blnfield,Calcdate=DateAdd("y",20,datefield),ca lcdate = DateAdd("y",8,datefield))

[Richie27]

Why use 2 dates. Have 1 Date Field and 1 boolean field for the time frame then you could just say IIF(blnfield,Calcdate=DateAdd("y",20,datefield),ca lcdate = DateAdd("y",8,datefield))

Thanks.

The calculation will be based on either one date field or the other so I need both date fields.

The fields on the form are called date_of_death and date_of_leaving.

If date of death is entered destruct due date* is 8 years from death date

If date of leaving is entered (but not date of death) destruct due date* is 20 years from date of leaving.

destruct_due_date is the calculated date.

I need to store this date in a table field called destruct_due_date.

Hope that's clearer than muddy water!

[R_Badger]

the simple answer is don't store it in a table.
Doing so is unnecessary, over complicates matters and is poor data management. If your client really wants it, it is available, it just isn't stored as data, because it is not data!

all you need is a query call the field whatever you like and use something like:
=iif([date of death] is null,[date of death]+(365*8),[date of leaving]+(20*365))

[RayMilhon]

first I agree with R Badger Storing a calculated field in a table is redundant and a waste. However my original suggestion still stands the boolean field can be designated as "yes = date of death"/" no = date of leaving."

[RayMilhon]

my original suggestion is since your previous post states the User requires the data to be stored. Still think it's a bad idea.

[Richie27]

Cheers. Where do you put this though?

Is it an unbound field on a form or what?

Sorry for my ignorance.

Do you set this in the after update event of the form and set the value of an unbound txt box to this if statement.

------------------------
stupid is as stupid does - Forest Gump ;-)

[Richie27]

What are the pit falls? Apart from violating yadda yadda.

On a small scale project that isn't likely to be altered much - is it that much of a big deal?

It will only be one little field.

[orange]

You can do anything you want. Store it if you have time to do it over, if and when necessary. Or do it according to Normalization rules and learn more about table structure and database design.


As for storing calculated values,
see http://allenbrowne.com/casu-14.html

[ssanfu]

Not getting into the right/wrong of it: the client wants it, the client get it.

Couldn't test it, but I modified your code. Try this:

Private Sub txtLeavingDate_AfterUpdate()
   Dim date1 As Date

   date1 = Forms!serviceUser!txtDeathDate
   
   Me![su_file subform].Form!file_destruction_due_date = DateAdd("yyyy", 20, date1)

End Sub

[Richie27]

Gosh darn! Those little syntax things kill me!

Well, when they said it I was like but, but, but....................but they don't care! They wants it they gets it, I shall remind of my but, but, buts should it all go horribly wrong...........

And sure if it does, I'll be in a super position to advise others of the perils.

When did anyone ever learn much from the mistakes of others in fairness?

Cheers Steve - on the nose as always!

[Richie27]

Well, I just realised the dangers of calculated fields!! DA DA DA.......

Person inputting data put it in the wrong field and then went to change it and wham - wrong data in calculated field.

Oh well - they wanted it........ thanks everyone.