If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to update a field with value from 2 calculated fields (dates)?
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. |
#2
|
|||
|
|||
How to update a field with value from 2 calculated fields (dates)?
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 "Chris1" wrote in message ... 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. |
#3
|
|||
|
|||
How to update a field with value from 2 calculated fields (dates)?
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Chris1" wrote in message ... 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. |
#4
|
|||
|
|||
How to update a field with value from 2 calculated fields (dates)?
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Chris1" wrote in message ... 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. . |
Thread Tools | |
Display Modes | |
|
|