Datediff on multiple dates in subform

[undee_69]

Good day accessforums people.

I just would like to ask how to get the date difference using subforms.

I have a main form that will count the number of days materials has been requested which it should check the latest dates on a subform. attached screenshot for your assistance.



is this possible?

[orange]

We need some context -- where does this form and requirement fit in a typical day in our business?
You telling us HOW you want to do something, but we don't know WHAT you are trying to accomplish.

Data exists in tables; forms only display the data. Why can't you run a query against the table(s) involved?

[undee_69]

Thank you for that. what i am trying to accomplish is to track all my materials request and how many days it has passed before all the materials were completely given to requestor. I am new to access and little programming. i am using query to get my data as of the moment but i don't know how to get the latest date using query. i am attaching my db and maybe you could assist me.

Purchase Request Tracking.zip

[orange]

I do not use macros. Perhaps someone else can assist.

Seems you have these tables

Jobs
Requestor
Request
Material

It also seems you have "partial deliveries/receipt" in that not all material associated with a request is delivered at the same time.

[undee_69]

ah yes.. I only would like to count the days that it completed all the purchased materials.

it will subtract the latest date of "completion date" in the subform from "requesting date" which is in the main form. but i cannot make it populate in the "completed days", also in main form.

[undee_69]

I al trying to do this on the beforeupdate but still won't pull out the date difference.

Private Sub daydiff_BeforeUpdate(Cancel As Integer)

Me.daydiff = DateDiff("ww", Forms!frmpurch!requestdate, Form!sfrmAccountNumber!max(completiondate))

[June7]

Why "ww" parameter?

Have a textbox in the subform footer section named tbxMax with an expression: =Max([CompletionDate])

Refer to tbxMax in textbox (or in code) on the main form:

=DateDiff("d", requestdate, [sfrmAccountNumber].Form.tbxMax)

Saving calculated data is usually a bad idea (calculate whenever needed). Will require code to save. I also only use VBA but the macro equivalent is SetValue action.

[undee_69]

I tried your suggestion @June7. I am able to get the max date if in form view but not in datasheet view. when I refer datediff to the textbox "daydiff" in the mainform, it is not showing the calculation.

i tried adding a text box in the main form with expression =Max(sfrmAccountNumber.Form.CompletionDate) but it is giving error. when is use "=Max(sfrmAccountNumber!Form!CompletionDate)", it does not show the max date on the subform.

Anything i am missing?

[June7]

My suggestion works for me even if subform is in datasheet view. The main form must be in Form or Continuous view. Doesn't sound like you did what I described.

[undee_69]

i think i may have followed your suggestion. unless i am missing something.

[undee_69]

i got it now. thank so much for the assistance.