How to filter last Issued date in orders

[June7]

Okay, need to use DEPTID and LOCID fields and the Department and Location pk values in the DMax. Add those fields to the Requisition query and add textboxes bound to those fields.



=DMax("[OrderDate]","Orders","DeptID=" & [DeptID] & " AND LocID=" & [LocID])

Or use the Requisition query as is as the data source for the DMax.
DMax("OrderDate", "Requisition", "Department='" & [Department] & "' AND Location='" & [Location] & "'")

Another option is in Report header or footer a textbox with:
=Max(OrderDate)

[glen]

Okay, need to use DEPTID and LOCID fields and the Department and Location pk values in the DMax. Add those fields to the Requisition query and add textboxes bound to those fields.

=DMax("[OrderDate]","Orders","DeptID=" & [DeptID] & " AND LocID=" & [LocID])

Thanks June.
this expression is working for me but the problem is when I make new order and put date today then in report the last date issue show today date.Bcoz it becomes max.

[June7]

You want the date just prior to this new order? Try:

=DMax("[OrderDate]","Orders","DeptID=" & [DeptID] & " AND LocID=" & [LocID] & " AND OrderDate<#" & [OrderDate] & "#")

[glen]

Thank you June it working fine now.

[glen]

June,
I want that if there is no last issue date then the box should blank or give some message like " First Order"

[June7]

I don't know why #Type! error, I would have expected #Error.

Try:
=Nz(DMax("[OrderDate]","Orders","DeptID=" & [DeptID] & " AND LocID=" & [LocID] & " AND OrderDate<#" & [OrderDate] & "#"),"First Order")

[glen]

Yes its working.
But now I realized that this error come when there is no selection in form. with empty form this error coming.
any solution for this?

[June7]

When there is no selection of what in which form? The main form?

I can work with Access 2010 database if you want to provide.

[glen]

yes Main form which name is Orders.
I attached my db you check and also check logon form there.

[June7]

There is a record in ORDER_DETAIL without an OrderID value. I set it to 3 and then all orders have related detail records and the DLookup works.

Suggest there is no reason to print an order that does not have any related Order detail records.

[glen]

Yes June I also set that value to 3 now all expressions working.Thanks