Returning one of many values on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 possible archive dates on a form. Each time a new assessment is made
on a claim the period of the award changes and therefore the archive date
changes. I want the archive date to change if and when a date is entered in
each box. The last date being the relevant date.
 
I have 4 possible archive dates on a form. Each time a new assessment is made
on a claim the period of the award changes and therefore the archive date
changes. I want the archive date to change if and when a date is entered in
each box. The last date being the relevant date.
Click to expand...

It sounds like you may be storing data redundantly, never a good idea!
What's the structure of your table? Why four fields for what sounds
like one archive date?

That said - you can use the AfterUpdate event of any textbox
containing a date to set the value of the ArchiveDate field, e.g.

Private Sub txtDate4_AfterUpdate()
If Me!txtArchiveDate < Me!txtDate4 Then
Me!txtArchiveDate = Me!txtDate4
End If
End Sub

John W. Vinson[MVP]
 
Thanks for response

Claims are assessed for a period of 3 yrs. It is sometimes necessary to
reassess claims for a different period and therefore different dates are
involved and different end dates. Difficult to explain in short. There could
possibly be 4 different end dates, therefore I need the Archive date to
change. I managed coding in the Archive box to return one of two values but
don't know how to do it for 4 values. Where would I put the coding you
suggested. I have 4 end date boxes and one Archive box.
 
Thanks for response

Claims are assessed for a period of 3 yrs. It is sometimes necessary to
reassess claims for a different period and therefore different dates are
involved and different end dates. Difficult to explain in short.
Click to expand...

Seems clear to me: you have a one to many relationship (in the real
world) between Claims and Assessments. One claim may have zero, one,
two or more assessments.

The proper data structure for this relationship is to have two tables,
Claims and Assessments, in a one to many relationship. You would add a
new *RECORD* (not a new field) to the Assessments table for each
reassessment.
There could
possibly be 4 different end dates, therefore I need the Archive date to
change. I managed coding in the Archive box to return one of two values but
don't know how to do it for 4 values. Where would I put the coding you
suggested. I have 4 end date boxes and one Archive box.
Click to expand...

If you need to check all four textboxes (which would not be necessary
in the properly normalized table, you'ld just use a Totals query),
you'll need to use code like:

Dim dtLatest As Date
If Not IsNull(Me!txtDate1) Then dtLatest = Me!txtDate1
If Not IsNull(Me!txtDate2) Then
If Me!txtDate2 > dtLatest Then dtLatest = Me!txtDate2
End If
If Not IsNull(Me!txtDate3) Then
If Me!txtDate3 > dtLatest Then dtLatest = Me!txtDate3
End If
If Not IsNull(Me!txtDate4) Then
If Me!txtDate4 > dtLatest Then dtLatest = Me!txtDate4
End If
Me!Archivedate = dtLatest


John W. Vinson[MVP]
 
Back
Top