Calculated Field Problem: Picking a date based on value in another field

[Dkline]

Please forgive me for asking about a calculated field in a table. I know it
shouldn't be done but I'm getting leaned on to figure out how to do this.

The controlling field can have a number of values. If the value is one of
three items "A", "B", or "C", they want another field in the same record to
be set to the date in the "BillThruDate" field. Else they want the date in
that calculated field to be set to the "TransactionDate".

Can this be done?

 

[Lynn Trapp]

You can't do it directly in the table, but in your data entry form you can
use the AfterUpdate event of your form to update the value of the record as
you need it.

 

[Dkline]

Thank you for the solution. I ended up with:

Private Sub Form_AfterUpdate()
Dim strTransactionType As String
strTransactionType = Me!TransactionType

Select Case strTransactionType
Case "COI Charge", "M&E Fee", "Loan Charge"
Me![Bill Date] = Me![BillThruDate]
Case Else
Me![Bill Date] = Me![TransactionDate]
End Select
End Sub

Works like a champ. Thanks again!

 

[Lynn Trapp]

Excellent!

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Thank you for the solution. I ended up with:

Private Sub Form_AfterUpdate()
Dim strTransactionType As String
strTransactionType = Me!TransactionType

Select Case strTransactionType
Case "COI Charge", "M&E Fee", "Loan Charge"
Me![Bill Date] = Me![BillThruDate]
Case Else
Me![Bill Date] = Me![TransactionDate]
End Select
End Sub

Works like a champ. Thanks again!

You can't do it directly in the table, but in your data entry form you can
use the AfterUpdate event of your form to update the value of the record as
you need it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


know
it record
to

date

in