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

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?

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


"Dkline" wrote in message
...
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?

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" wrote in message
...
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


"Dkline" wrote in message
...
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?

Excellent!

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


"Dkline" wrote in message
...
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" wrote in message
...
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


"Dkline" wrote in message
...
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?