How to filter last Issued date in orders

[glen]

Hi friends,
I created one database for orders.there are too many orders for same department and location with different order numbers and issued date.
I put one field there in Report for Last issued date.
So I want that when i make new order for same department and location it should show me the last issued date of last order?
Thanks

[June7]

This type of value (data dependent on other data) should not be saved into table. Calculate it when needed. A DMax expression in the textbox could accomplish this.

However, if you must you can either manually enter the value or run an SQL UPDATE action or after the form is open, use code to set the value of the textbox.

[glen]

Plz tell me in detail how to do ?
I don't want to do it manually.

[June7]

The real trick is figuring out what event to put code in. I use only VBA, not macros.

Don't know enough about your db to offer details at this time.

Is thumbnail 3 a report object or is it really a form? Is it opened by the 'Open Report' button? Show the code for this button.

[glen]

Yes thumbnail 3 is Report object.
I just made this "Open Report" By command Button Report operation to preview Report.
The code behind this button is as below:

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String

stDocName = "REQUISTION"
DoCmd.OpenReport stDocName, acPreview

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub

[June7]

You say "I put one field there in Report for Last issued date." Is this a textbox bound to a field of the report RecordSource? If yes, what is the table name and field name? Code could be above the OpenReport line, like:
CurrentDb.Execute "UPDATE tablename(LastIssueDate) VALUES(#" & [OrderDate] & "# WHERE Department='" & Me.Department & "' AND Location='" & Me.Location & "'"


If it is not a bound textbox then need an expression in the report query or in the textbox that will lookup the last issued date. Is the last issued date the last purchase order for a department and location? Expression like:
DMax("OrderDate", "Orders", "Department='" & [Department] & "' AND Location='" & [Location] & "'")


Use your table and field names. The example assumes Department and Location are text values.

[glen]

June your this code is working
DMax("OrderDate", "Orders", "Department='" & [Department] & "' AND Location='" & [Location] & "'")


But it does not identify the Department and location id but just take the last issue date from orders table.

[glen]

and I want to show the quantity in 01,02,03 format not 1,2,3 .
what setting for this in properties?
Thanks

[June7]

Where did you put the DMax expression? If in a query, show the SQL statement.

Format(Quantity, "00")
The result is a string, not a number.

[glen]

I put the expression in that unbound text box .

[June7]

In the Detail section? Are the fields Department and Location included in the RecordSource?

Want to provide db for analysis? Follow instructions at bottom of my post.

[glen]

Here is record source for Report.

[June7]

Guess I will have to see db to analyze.

[glen]

Here is my database.

[glen]

Sorry by mistake I uploaded the old copy of my this database.
I m trying to save as .mdb my current copy but it give message you cannot save in earlier version.