Help with multiple criteria DLookup

[jtm013]

Hello,



I know there are several Dlookup threads already, but I have still been unable to develop a solution.

I have a DB that tracks tooling. Every time a tool is checked out to a user a record is created in table "tlogIssued", this record stores (among other things) the tool's ID, the date the tool is being checked out, and it also adds a status note indicating if the tool is "checked out" or "checked in".

I am adding a functionality to scrap tools, and I need to log every occurrence. On the new scrap log one of the things that is desired to be recorded is when the tool was last checked out.

Enter my dlookup problem- I need my dlookup to draw the 'lastissueddate' from the tlogIssued table, the only way I have come up to do this is by using multiple criteria and having the dlookup search by "toolID" (which works fine on its own) and then somehow (and this is where I am lost) by the last occurrence of the words "checked out" in the status note field (called field "InOut") for that particular toolID.

My code currently looks as follows:

DLookup("issuedOn", "tlogIssued", "[toolId]=" & Me.bxToolID.Value & " And [InOut]= " & Checked Out")

I know the above code has incorrect syntax, and I also realize that it won't find the last occurrence of the "Checked Out" status identifier.

Any and all help is appreciated.

[orange]

Do you need to know to whom the Tool was assigned/given to?
Your process seems very similar to the Loan of a Library Book.

Customer borrows Book(with Title = "TITLE") on DateX to be returned on or before DateY.

[ranman256]

Try NOT to use Dlookup. Slooowwww.
use a query with parameter from the form: forms!frmMain!bxToolID

i myself open a continuous form on the person. It shows all items of that person, date descending. Then I can scroll thru.

[jtm013]

Do you need to know to whom the Tool was assigned/given to?
Your process seems very similar to the Loan of a Library Book.

Customer borrows Book(with Title = "TITLE") on DateX to be returned on or before DateY.

Orange- The idea is the same. As far as logging the check out/check in process that is established and functions well. The data is stored in the aforementioned table tlogIssued.

The process I'm trying to implement now, would be analogous to removing a book from the library collection permanently. However, in this case I need to be able to record the last time the tool was checked out.

99% of what I am using to log the check out/ check in process works to log the scrapping of the tool. However, the dlookup I'm trying to get to work is needed (unless there is a better way) to go in and find the last instance the tool was checked out and then insert that date into a field. I know how to insert the data once found, I just can't figure out how to look up the last checked out date. As far as knowing who last checked out the tool, yes I need to know that as well, but I figured if I can get the dlookup figured out to find the last checked out date, then it is just a matter of redirecting the dlookup to a different field in table tlogIssued in order to find who the tool was last checked out to. Hopefully that is more clear.

Try NOT to use Dlookup. Slooowwww.
use a query with parameter from the form: forms!frmMain!bxToolID

i myself open a continuous form on the person. It shows all items of that person, date descending. Then I can scroll thru.

Ranman- So hopefully the above description provides some clarification. I'm not against a continuous form to display the data. Unfortunately we are not set up to where each user has a running history. The idea in lieu of that, is that there is a running log of tools which are scrapped. This log (table) can then be queried and reports made for varying specifics. The methodology of input into this scraplog/table is that when a tool's detailed information page is edited and tool location is changed to show "scrap" then a before update event is triggered which collects all the required data and writes it to the scraplog/table. The only piece of information I am unable to collect is the last checked out date. (As mentioned above, I do also need to know who it was checked out to, but if I can get one I can get the other.)

[orange]

Please post a jpg of your relationships window.

[jtm013]

Please post a jpg of your relationships window.

Here it is.

[jtm013]

Well I figured out a solution- rather than try to have everything done programmatically, I created a form which allows the data to entered into the scrap log/table. Then using queries I was able to make the last issued to/on information select-able in combo boxes on my form.

[orange]

Great. So you have it solved?