field updates to a last record for that data

[Guest]

hello, I have a major question,

I have a form that has a date returned field, and i have another form that
has a different date return field with multiple date returns for a single
record. what i am trying to do is to make one of the fields have the last
date returned (from the one with multiple dates in a single record) if a
certain crieteria is met.

the code i believe starts out like:

If Me![Qty Returned].Value = Me![Qty].Value Then
Me![Date Returned].Value = .... (this is where i have the problem, how
can i get it to add in the last date of the Form!IssueReturnLog![Date
Returned]. (last date)

does that make any sense? and can anyone help me?
Thanks in advance.

Jay

 

[John Vinson]

hello, I have a major question,

I have a form that has a date returned field, and i have another form that
has a different date return field with multiple date returns for a single
record. what i am trying to do is to make one of the fields have the last
date returned (from the one with multiple dates in a single record) if a
certain crieteria is met.

This is probably A Very Bad Idea. If you store this maximum date and
another, later, record gets added to the second table, your stored
date IS NOW WRONG, with no good way to assure that it is correct. If
you want to find the latest return date, you're almost surely better
off to use a Totals query or a DMax() function call - see below - to
calculate it on the fly.

the code i believe starts out like:

If Me![Qty Returned].Value = Me![Qty].Value Then
Me![Date Returned].Value = .... (this is where i have the problem, how
can i get it to add in the last date of the Form!IssueReturnLog![Date
Returned]. (last date)

Data is NOT stored in Forms. A Form is just a tool, a window, a way to
update data in a Table. You cannot find the "last date" in a Form any
more than I can collect rocks from the Owyhee Mountains by reaching
out to my office window.

Instead, use the DMax() function to search the table for the maximum
value of the date field:

Me![Date Returned] = DMax("[Date Returned]", "[Your Table Name]",
"[ID] = " & Me![ID])

where ID is the unique identifier of the record.

John W. Vinson[MVP]

 

[Jeff Boyce]

I don't have a clear picture of your underlying data. Since Access stores
data in table fields, not in forms, it would help to understand how your
data is structured.

For example, I am not clear on what you mean when you describe a "date
return field with multiple date returns for a single record." Does this
mean you are storing more than one fact in one field (a table field, not a
control on a form)?