Dmax, Dmin in Unbound textbox forms based on Date criteria, Wrong number display?

[radian89]

Hi All,



wish you could help me, on Christmas... I had this form & situation,
1. I have 2 unbound textboxes which show StartDate & EndDate
2. I made 2 unbound textboxes again, which looking for the Highest & lowest Invoice number based on 1st above condition (StartDate & EndDate)

For the lowest Invoice number, lookup date from StartDate textbox, for the highest Invoice no, lookup date from EndDate textbox.
Using Dmax & Dmin, here's the screenshot of the form & how i wrote the formula.



Invoice number taken from table InvoiceT, here's the sreenshot of the table



The question is, when I input for example :
- start date 12/17/2015, it should show Invoice no 2 (for the lowest), instead it show 1.
- end date 12/16/2015, it should show invoice no 1 (for the highest), instead it show 4.




anything i missed on the formula?

Thanks for the help....

[JeroenMioch]

If i understand your problem correct its a sorting problem in your form or subform.
Go to the property's of your form and look for "Sort by".
There you should have the name of the field in your form.

[radian89]

If i understand your problem correct its a sorting problem in your form or subform.
Go to the property's of your form and look for "Sort by".
There you should have the name of the field in your form.

Hi JeroenMich,
Is it "Order By"? it's on my form
if yes, What should I do with it?

thanks for the prompt reply & help

[JeroenMioch]

Yes its Order by, sorry.
Put the name of the field you want to sort there. So in your case its "Invoice date".
Im not sure but it is possible that access doesnt do it because you have a space between the words invoice and Date.
In that case try to put them between brackets like this [Invoice date].

Furthermore, im not sure if the sorting is applied while you update records. It could be that the sorting is done whenever you open the form.
If so, you can program it in VBA in the afterupdate event of the (sub) form.

[radian89]

Yes its Order by, sorry.
Put the name of the field you want to sort there. So in your case its "Invoice date".
Im not sure but it is possible that access doesnt do it because you have a space between the words invoice and Date.
In that case try to put them between brackets like this [Invoice date].

Furthermore, im not sure if the sorting is applied while you update records. It could be that the sorting is done whenever you open the form.
If so, you can program it in VBA in the afterupdate event of the (sub) form.

Okay, you mean the Invoice Date on table InvoiceT, right? If yes, I'll write on the form's Order by : [InvoiceT]![Invoice Date]... If not, is it the Invoice date from the subform?

I still don't know the connection behind sorting with this.... Hehehhehehhe.... Mind if you explain lil' bit bout it? 😄

I'll try it tomorrow morning (it's midnite here). Thanks a lot for the help JeroenMioch, have a great Christmas

[JeroenMioch]

Its invoice date of the subform.

You can sort your table, queries etc but your (sub)form represents what you want to see.
In this case you want your subform to show everything between your selected criteria.
These criteria can be between two dates, or invoice numbers. The form however does not know
which values need to be on top of the form untill you define this.
By default it will select one of the fields, normally i would expect the primary key which is usually an autonumbering field.

So you select your subform and select property's. By "Order by" you enter the name of the field you want
to sort by. In your case this is Invoice date. By default, it will select ascending order unless you tell it otherwise.
If you want a descending order, you enter Invoice date DESC or [Invoice date] DESC

If the recordsource of the subform is a query you can sort the underlaying query to achieve the same effect by the way.

Let me know if it works.

Merry Christmas !

[June7]

Domain aggregate might not be needed. Are you using the start and end dates to filter the subform recordset? Do you want to display the minimum and maxium invoice numbers of that filtered dataset?

Expression in textbox of form header or footer can be:

=Min([InvoiceNum])

=Max([InvoiceNum])

This will return the minimum and maximum invoice numbers for the filtered dataset on the form.

Put these textboxes in the subform. However, since the subform is in Datasheet view, will need textboxes on the main form that reference the textboxes on the subform.

=[subformcontainername].Form.[textboxname]

Otherwise, the domain aggregate would be:

=DMin("InvoiceNum", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

Sort order has no bearing on these expressions.

How are you filtering the subform? Method I prefer: http://www.allenbrowne.com/ser-62.html

[radian89]

Its invoice date of the subform.

You can sort your table, queries etc but your (sub)form represents what you want to see.
In this case you want your subform to show everything between your selected criteria.
These criteria can be between two dates, or invoice numbers. The form however does not know
which values need to be on top of the form untill you define this.
By default it will select one of the fields, normally i would expect the primary key which is usually an autonumbering field.

So you select your subform and select property's. By "Order by" you enter the name of the field you want
to sort by. In your case this is Invoice date. By default, it will select ascending order unless you tell it otherwise.
If you want a descending order, you enter Invoice date DESC or [Invoice date] DESC

If the recordsource of the subform is a query you can sort the underlaying query to achieve the same effect by the way.

Let me know if it works.

Merry Christmas !

Domain aggregate might not be needed. Are you using the start and end dates to filter the subform recordset? Do you want to display the minimum and maxium invoice numbers of that filtered dataset?

Expression in textbox of form header or footer can be:

=Min([InvoiceNum])

=Max([InvoiceNum])

This will return the minimum and maximum invoice numbers for the filtered dataset on the form.

Put these textboxes in the subform. However, since the subform is in Datasheet view, will need textboxes on the main form that reference the textboxes on the subform.

=[subformcontainername].Form.[textboxname]

Otherwise, the domain aggregate would be:

=DMin("InvoiceNum", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

Sort order has no bearing on these expressions.

How are you filtering the subform? Method I prefer: http://www.allenbrowne.com/ser-62.html

Hi Jeroen & June,

thanks for the reply, I've tried both solution, and its still doesn't work. The one that close enough is

=DMin("InvoiceNum", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

But the result is error. Maybe there's some misunderstanding here, let me explain

I use the From Date & End Date for filtering the subform, but the subform (or the query for the subform) doesn't have Invoice number in it. The filtering works by click button command11, but just only filtering, not the Invoice number. It's 2 different case, and not related at all.

The text box From Date & End Date works as filter for subform and also looking Invoice number from InvoiceT table.

all the 4 textboxes are unbound.

Thanks a lot for the help
If you don't mind, I attached the database for you to look at.
the issue is in "DailyTransSummF" form & "InvoiceT" table, and the subform data came from "DailyTransReportBreakdownItemQ" Query

Database11.zip

[June7]

The db is not using the expressions I suggested. I edited and tested. I entered 12/17/2015 in both date boxes and results 2 and 4 are calculated by the DMin and DMax. If this is not what you expect - why? What values do you want returned?

[radian89]

The db is not using the expressions I suggested. I edited and tested. I entered 12/17/2015 in both date boxes and results 2 and 4 are calculated by the DMin and DMax. If this is not what you expect - why? What values do you want returned?

Really? that's what i really expected... how to write the function then? when i was trying (with your suggestion) it shown either 1, or error for the minimum, it's my fault then.

[June7]

I put these expressions in the textboxes:

=DMin("InvoiceNumber", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

=DMax("InvoiceNumber", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

[radian89]

I put these expressions in the textboxes:

=DMin("InvoiceNumber", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

=DMax("InvoiceNumber", "InvoiceT", "InvoiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

aw....
Thanks a lot June7, it works thanks for the Christmas gift.
may be I copied it wrong & misstypo, that's end up in error.

cheers, Merry Christmas

[June7]

My original example used InvoiceNum but I found out the actual field name is InvoiceNumber.