How do I hav one field determine value of another field

D

Dana Pike

I have four fields, A,B,C,D; Where the values for B,C,D are Dates
IF A is AI then D=B+600
If A is AA then D=C+365
If A is AC then input D
 
J

John Vinson

On Tue, 13 Dec 2005 14:28:02 -0800, Dana Pike <Dana
I have four fields, A,B,C,D; Where the values for B,C,D are Dates
IF A is AI then D=B+600
If A is AA then D=C+365
If A is AC then input D
Click to expand...

What if B is null? or C? What if either is an invalid date? What if A
is XY? At what point do you want the value of D to be set? What order
will A, B and C be entered? What do you want to happen if A is AA; D
gets set; and A gets changed to AC? Is this on a Form (just possible)
or in a Table (not possible)?


John W. Vinson[MVP]
 
D

Dana Pike

John Vinson said:
On Tue, 13 Dec 2005 14:28:02 -0800, Dana Pike <Dana


What if B is null? or C? What if either is an invalid date? What if A
is XY? At what point do you want the value of D to be set? What order
will A, B and C be entered? What do you want to happen if A is AA; D
gets set; and A gets changed to AC? Is this on a Form (just possible)
or in a Table (not possible)?


John W. Vinson[MVP]
Click to expand...
B can not be Null, C can only be null if A is AI. A can only be AI, AA or AC.
B will be the only field not changed after initial input. When A is changed
I need D to change. This will be on a form. B, C and D are formated for
medium date.
 
J

John Vinson

B can not be Null, C can only be null if A is AI. A can only be AI, AA or AC.
B will be the only field not changed after initial input. When A is changed
I need D to change. This will be on a form. B, C and D are formated for
medium date.
Click to expand...

OK... then, in the AfterUpdate event of the control for A (a Combo Box
or Listbox I presume) put code like:

Private Sub cboA_AfterUpdate()
SELECT Case Me!cboA
Case "AI"
Me!txtD = DateAdd("d", 600, Me!txtB)
Case "AA"
Me!txtD = DateAdd("yyyy", 1, Me!txtC)
' use "d", 365 if you want literally 365 days, e.g. to have
' June 1, 2007 give you May 31, 2008 because of leapyear
Case "AC"
Me!txtD.SetFocus
MsgBox "Enter a date in D", vbOKOnly
Case Else
MsgBox "oops, this should never happen", vbOKOnly
End Select
End Sub


John W. Vinson[MVP]
 
Top