A
Adam Ryba
Hello all,
I have been trying to figure out how to search through a subform for some
records that have 3 potential values in one field (albeit only one of these
will be listed for a given nest) and a single value in another field.
I have a nest table (plover_table) that has the nest, and a visits table
(nest_visits) (one-to-many relationship).
Nests will be checked a few times per week, I have a field in the subform
called Stage that includes: Lay, Incubation, Abandoned, Destroyed, Chicks,
Fledged, Unknown, and Successful. On the main form, I have a field that has
the termination date (this is when the nest is either destroyed, successful,
or abandoned) but a twist on it is I need to take the date of the record when
the nest was either destroyed, successful or abandoned, and the date from the
previous visit and split the difference (the way I think to do this is to
first sort on the date, convert the dates to julian, add them together and
divide by 2, then convert it back), but if the nest is checked on the day the
eggs have hatched, then use that date as the one that is put into the
termination date.
So, what I really need is where to begin writing the code. I am guessing I
first have to set the nest_visits subform to a recordset (clone?), sort the
recordset by date, then find the record that has Abandoned, Destroyed or
Successful in it (and if it wasn’t a hatch day), place the date from that
record in a temporary place, then moveprevious and put this date in a
temporary place. I think I can write the datediff calculation after I get
the dates into the controls, but have been unsuccessful so far. This is what
I have atm, but haven’t gotten it to work as of yet:
Dim check_subform As Form
Set check_subform = Me.Nest_check_table_subform.Form
With check_subform.RecordsetClone
check_subform.FindFirst "[Stage] = 'Abandoned'" Or "[Stage] =
'Destroyed'" Or "[Stage] = 'Successful'"
If check_subform.[Stage].Value = "Successful" And
check_subform.[Hatch_day?].Value = -1 Then
Temp_Term = check_subform.Date
Else
ctl_end_date = check_subform.Date
check_subform.MovePrevious
ctl_prev_date = check_subform.Date
End If
End With
Any help or suggestions would be appreciated
Adam
I have been trying to figure out how to search through a subform for some
records that have 3 potential values in one field (albeit only one of these
will be listed for a given nest) and a single value in another field.
I have a nest table (plover_table) that has the nest, and a visits table
(nest_visits) (one-to-many relationship).
Nests will be checked a few times per week, I have a field in the subform
called Stage that includes: Lay, Incubation, Abandoned, Destroyed, Chicks,
Fledged, Unknown, and Successful. On the main form, I have a field that has
the termination date (this is when the nest is either destroyed, successful,
or abandoned) but a twist on it is I need to take the date of the record when
the nest was either destroyed, successful or abandoned, and the date from the
previous visit and split the difference (the way I think to do this is to
first sort on the date, convert the dates to julian, add them together and
divide by 2, then convert it back), but if the nest is checked on the day the
eggs have hatched, then use that date as the one that is put into the
termination date.
So, what I really need is where to begin writing the code. I am guessing I
first have to set the nest_visits subform to a recordset (clone?), sort the
recordset by date, then find the record that has Abandoned, Destroyed or
Successful in it (and if it wasn’t a hatch day), place the date from that
record in a temporary place, then moveprevious and put this date in a
temporary place. I think I can write the datediff calculation after I get
the dates into the controls, but have been unsuccessful so far. This is what
I have atm, but haven’t gotten it to work as of yet:
Dim check_subform As Form
Set check_subform = Me.Nest_check_table_subform.Form
With check_subform.RecordsetClone
check_subform.FindFirst "[Stage] = 'Abandoned'" Or "[Stage] =
'Destroyed'" Or "[Stage] = 'Successful'"
If check_subform.[Stage].Value = "Successful" And
check_subform.[Hatch_day?].Value = -1 Then
Temp_Term = check_subform.Date
Else
ctl_end_date = check_subform.Date
check_subform.MovePrevious
ctl_prev_date = check_subform.Date
End If
End With
Any help or suggestions would be appreciated
Adam