How to update a field with value from 2 calculated fields (dates)?

[Chris1]

Hi

I really need some help on this one:

I have one table containing tools to be maintained:
There is a [Period} field which can contain the following
values: 1 Year, 6 months, and 3 months.
Each tool will be matched with one of these periods.

The second field contains date when last check was done
[LastChecked].

The third field [NextDue]is the one that needs to
calculate from the 2 above; For example if I have a tool
checked on the 24 May 04, and its period is 6 months, I
would like to see the result of 24 May 04 + 6 months
automatically in the third field.

Do I need a condition in the validation rule in the
table?, or should I have an expression in queries? In
either case what would that be?

Many Thanks,
Chris.

 

[Rick B]

It is not good database design to store calculated numbers. You can
calculate them on the fly. Any form that displays this info could contain a
field that calculates the value for you. Any report could do the same.

If you *DID* store it, what would prompt it to change when the 'checked out'
date changes? Are you going to run an update query each day?

You should not store this date.

Rick B


Hi

I really need some help on this one:

I have one table containing tools to be maintained:
There is a [Period} field which can contain the following
values: 1 Year, 6 months, and 3 months.
Each tool will be matched with one of these periods.

The second field contains date when last check was done
[LastChecked].

The third field [NextDue]is the one that needs to
calculate from the 2 above; For example if I have a tool
checked on the 24 May 04, and its period is 6 months, I
would like to see the result of 24 May 04 + 6 months
automatically in the third field.

Do I need a condition in the validation rule in the
table?, or should I have an expression in queries? In
either case what would that be?

Many Thanks,
Chris.

 

[Allen Browne]

Split your Period field into two: a number field named (say) Freq, and a
text field name PeriodTypeID

Create a table containing the valid values for the period type. 2 fields,
named PeriodTypeID and PeriodType. The records will be:
d Days
m Months
q Quarters
yyyy Years

You could then update the NextDue field with event procedures like this:
--------------code starts-------------
Private Sub LastChecked_AfterUpdate()
If Not (IsNull(Me.LastChecked) Or IsNull(Me.Freq) Or
IsNull(Me.PeriodTypeID)) Then
Me.NextDue = DateAdd(Me.Freq, Me.PeriodTypeID, Me.LastChecked)
End If
End Sub

Private Sub Freq_AfterUpdate()
Call LastChecked_AfterUpdate
End Sub

Private Sub PeriodTypeID_AfterUpdate()
Call LastChecked_AfterUpdate
End Sub
------------code ends-------------

It may be better not to store NextDue at all, but to calculate it from the
Max value of LastChecked. See:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

 

[Chris1]

Thank you,
This will be very helpful.
Chris1

-----Original Message-----
Split your Period field into two: a number field named (say) Freq, and a
text field name PeriodTypeID

Create a table containing the valid values for the period type. 2 fields,
named PeriodTypeID and PeriodType. The records will be:
d Days
m Months
q Quarters
yyyy Years

You could then update the NextDue field with event procedures like this:
--------------code starts-------------
Private Sub LastChecked_AfterUpdate()
If Not (IsNull(Me.LastChecked) Or IsNull(Me.Freq) Or
IsNull(Me.PeriodTypeID)) Then
Me.NextDue = DateAdd(Me.Freq, Me.PeriodTypeID, Me.LastChecked)
End If
End Sub

Private Sub Freq_AfterUpdate()
Call LastChecked_AfterUpdate
End Sub

Private Sub PeriodTypeID_AfterUpdate()
Call LastChecked_AfterUpdate
End Sub
------------code ends-------------

It may be better not to store NextDue at all, but to calculate it from the
Max value of LastChecked. See:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I really need some help on this one:

I have one table containing tools to be maintained:
There is a [Period} field which can contain the following
values: 1 Year, 6 months, and 3 months.
Each tool will be matched with one of these periods.

The second field contains date when last check was done
[LastChecked].

The third field [NextDue]is the one that needs to
calculate from the 2 above; For example if I have a tool
checked on the 24 May 04, and its period is 6 months, I
would like to see the result of 24 May 04 + 6 months
automatically in the third field.

Do I need a condition in the validation rule in the
table?, or should I have an expression in queries? In
either case what would that be?

Many Thanks,
Chris.

.