Help looking up the last product in a container

[Roger]

I have a data base that tells production what porduct to put in what trailer
and I need to be able to find out what the last product was in the trailer.
All this information is in the table on the last trailer by product name,
trailer number and date returned. Can someone help me with this?

 

[Allen Browne]

Use DLookup() to get the previous product.

This example assumes a table named tblLoad, with fields like this:
TrailerID Number what trailer is used for the load.
ProductID Number what product was carried.
LoadDate Date/Time when this happened.

The code would be:
Dim strWhere As String
Dim varResult As Variant
If Not (IsNull(Me.TrailerID) Or IsNull(Me.LoadDate)) Then
strWhere = "(TrailerID = " & Me.TrailerID & ") AND (LoadDate < " &
Format(Me.LoadDate, "\#mm\/dd\/yyyy\#") & ")"
varResult = DLookup("ProductID", "tblLoad", strWhere)
If Not IsNull(varResult) Then
MsgBox "Last ProductID was " & varResult
End If
End If

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

If you want to do this for all records, use a subquery:
http://allenbrowne.com/subquery-01.html

 

[Guest]

I have a data base that tells production what porduct to put in what
trailer
and I need to be able to find out what the last product was in the
trailer.
All this information is in the table on the last trailer by product name,
trailer number and date returned. Can someone help me with this?

 

[Guest]

å·®è·å·®è·ï¼Œæœ‰äººéƒ½å‘ä¸å‡ºï¼Œæœ‰äººéƒ½ç”¨è‹±æ–‡å•¦!

 

[Roger]

Ok a little more info I have a field called last product on trailer and now I
am just looking up manualy and finding what the last product was and typing
it in so is this still what I should use to get this answer?

When I put this in it says that "Invalid SQL statement; xepected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATED'."
Should I put in here INSERT into "Last Product on Trailer" in the Table
called Orders

The code would be:

Dim strWhere As String
Dim varResult As Variant
If Not (IsNull(Me.TrailerID) Or IsNull(Me.LoadDate)) Then
strWhere = "(TrailerID = " & Me.TrailerID & ") AND (LoadDate < " &
Format(Me.LoadDate, "\#mm\/dd\/yyyy\#") & ")"
varResult = DLookup("ProductID", "tblLoad", strWhere)
If Not IsNull(varResult) Then
MsgBox "Last ProductID was " & varResult
End If
End If

This would give me a message on the last product on the trailer but I would
want it to go to a "Last Product in trailer" field in the tblLoad. How is
this done.
Roger Perkins
(e-mail address removed)

Use DLookup() to get the previous product.

This example assumes a table named tblLoad, with fields like this:
TrailerID Number what trailer is used for the load.
ProductID Number what product was carried.
LoadDate Date/Time when this happened.

The code would be:
Dim strWhere As String
Dim varResult As Variant
If Not (IsNull(Me.TrailerID) Or IsNull(Me.LoadDate)) Then
strWhere = "(TrailerID = " & Me.TrailerID & ") AND (LoadDate < " &
Format(Me.LoadDate, "\#mm\/dd\/yyyy\#") & ")"
varResult = DLookup("ProductID", "tblLoad", strWhere)
If Not IsNull(varResult) Then
MsgBox "Last ProductID was " & varResult
End If
End If

For help with DLookup(), see:
http://allenbrowne.com/casu-07.html

If you want to do this for all records, use a subquery:
http://allenbrowne.com/subquery-01.html

 

[Allen Browne]

No! Don't store the last product!

One of the basic rules of data normalization is to never store dependent
values. Instead, get the database to look it up for you, using a DLookup()
if you just need one value, or a subquery when you need it for every record.