Update a date on main Form

[JOM]

I have a datasheet subform that has [Receiveddate] column that has several
dates in it and a combobox that has [status]. What I would ike to do is
update a textbox on the main form with the latest date in the [Receiveddate]
column from the subform if status is not equal to pending.

E.g., Status ReceivedDate
Complete 12/25/05
Complete 12/25/05
complete 12/29/05

So the date I would ike to have on mainform is 12/29/05

 

[schasteen]

I am assuming this is a continuous form.
An easy way to do this would be to add a textbox with the control source:
=iif([Status] <> "pending",[Receiveddate],"")
then in the forms footer place the textbox with the control source:
=max([Textbox]) where [Textbox] is the control created above

I have not tested this so it may take a little tweaking. Hide the first
textbox you created. If my assumtion is wrong, you will have to take a
different approach.

 

[JOM]

Thanks for the response, however, the response worked partially. The subform
is a continous form, some records could have pending and some could have
Completed or cancelled, so what its doing is that the for the record that has
the mixed status, its still putting the max date, so how will take care of
this?

I am assuming this is a continuous form.
An easy way to do this would be to add a textbox with the control source:
=iif([Status] <> "pending",[Receiveddate],"")
then in the forms footer place the textbox with the control source:
=max([Textbox]) where [Textbox] is the control created above

I have not tested this so it may take a little tweaking. Hide the first
textbox you created. If my assumtion is wrong, you will have to take a
different approach.

I have a datasheet subform that has [Receiveddate] column that has several
dates in it and a combobox that has [status]. What I would ike to do is
update a textbox on the main form with the latest date in the [Receiveddate]
column from the subform if status is not equal to pending.

E.g., Status ReceivedDate
Complete 12/25/05
Complete 12/25/05
complete 12/29/05

So the date I would ike to have on mainform is 12/29/05