Multiple Criteria Dlookup?

[liutofsik]

Hello and thanks for taking a look,

Riddle you this, I will. :banghead:

Trying to migrate from Excel to Access to track Trucking Operations. Building a query for a datasheet form down the road. It is meant to be a dashboard DS to show minimal information.

Simplified, these tables are in the query:
LoadT
LoadID

LoadStopT
LoadStopID
LoadID_FK
StopOrder
StopID_FK

StopT
StopID
StopCity
StopState
StopZipCode

Each record will display a unique load.
Each record needs to show the first stop and last stop.
Stops, right now, are entered in short text 1-9 by field [LoadStopT]![StopOrder].

Building: DLookUp("[StopCity]&', '& [StopState] &' '& [StopZipCode]","StopT","[StopID]=" & [StopID_FK])

Gives me what I want to see but my problem is trying to combine the DlookUp of the City, State, and Zip with the criteria of StopOrder=1 and for the second field StopOrder=DMax?

So far I can't find any resource and I just feel so dumb because I can't figure this out..Am I even trying something possible?

Any criticism is much appreciated.

 

[liutofsik]

I was under the impression the LastStop field would need to be something like this:

laststop: DLookUp("[StopCity]&', '& [StopState] &' '& [StopZipCode]","StopT","[StopID]=" & DLookUp("[StopID_FK]","LoadStopT","[StopOrder]=" & DMax("[StopOrder]","LoadStopT","[LoadID_FK]=" & [LoadID])))

but it just returns an error

 

[Mihail]

Maybe this discussion
http://www.access-programmers.co.uk/forums/showthread.php?t=103343
will reveal the right way for you.

 

[liutofsik]

Came across that thread and unfortunately it doesn't help. I need to look for location info from one table, StopT, and the StopOrder from a different table, LoadStopT. Bobadopolis's DlookUp criteria was for the same table.

 

[liutofsik]

This doesn't work either..

firststop: DLookUp("[StopCity]&', '& [StopState] &' '& [StopZipCode]","StopT","[StopID]= ' " & [StopID_FK] & " ' And " & [LoadStopT]![StopOrder]=' " & "1" & " ')

I'm so lost and I don't understand why this doesn't work?

 

[Mihail]

Came across that thread and unfortunately it doesn't help. I need to look for location info from one table, StopT, and the StopOrder from a different table, LoadStopT. Bobadopolis's DlookUp criteria was for the same table.

Do you know that a query can be used exactly like a table ?
Create a query with necessary information from both tables then use the DLookup using this query's fields.

 

[liutofsik]

A query is what I'm trying to make. Let me rephrase it a little. If I make a query with every stop I will be multiplying all my data. I feel it would be pointless to do that when my LoadStopT is already neatly organized according to LoadID_FK and StopOrder.

query should look like this:

| LoadID_FK | LoadStopID_FK_First | LoadStopID_FK_Last |

I need to hide any stops in between first and last, and the fields need to be concatenated to show company, address, city, state and zip code according to StopID in StopT by StopID_FK returned from LoadStopT according to the current LoadID.

However, until this day I've yet to find a solution or a workaround...:banghead: