Help looking up the last product in a container

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?
-- 
Roger Perkins
rperk60@swbell.net
0
Utf
3/17/2008 2:39:00 AM
access 16762 articles. 3 followers. Follow

5 Replies
688 Views

Similar Articles

[PageSpeed] 5

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 - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Roger" <rperk60@swbell.net> wrote in message
news:28DBA8B5-4391-46D0-B16C-1733318B63EA@microsoft.com...
>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?
> -- 
> Roger Perkins
> rperk60@swbell.net 

0
Allen
3/17/2008 3:19:03 AM

"Roger" <rperk60@swbell.net> 写入消息 
news:28DBA8B5-4391-46D0-B16C-1733318B63EA@microsoft.com...
> 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?
> -- 
> Roger Perkins
> rperk60@swbell.net 

0
wise7
3/17/2008 1:49:57 PM
差距差距,有人都发不出,有人都用英文啦!
"Roger" <rperk60@swbell.net> 写入消息 
news:28DBA8B5-4391-46D0-B16C-1733318B63EA@microsoft.com...
> 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?
> -- 
> Roger Perkins
> rperk60@swbell.net 

0
wise7
3/17/2008 1:51:37 PM
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
rperk60@swbell.net


"Allen Browne" wrote:

> 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 - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Roger" <rperk60@swbell.net> wrote in message
> news:28DBA8B5-4391-46D0-B16C-1733318B63EA@microsoft.com...
> >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?
> > -- 
> > Roger Perkins
> > rperk60@swbell.net 
> 
> 
0
Utf
3/17/2008 4:29:02 PM
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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Roger" <rperk60@swbell.net> wrote in message
news:89FF697C-DA4B-4205-B779-593C965348CC@microsoft.com...
> 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
> rperk60@swbell.net
>
>
> "Allen Browne" wrote:
>
>> 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
>>
>> "Roger" <rperk60@swbell.net> wrote in message
>> news:28DBA8B5-4391-46D0-B16C-1733318B63EA@microsoft.com...
>> >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?
>> > -- 
>> > Roger Perkins
>> > rperk60@swbell.net 

0
Allen
3/18/2008 12:26:31 AM
Reply:

Similar Artilces:

Help with WSDL (XSD) compiler
Hi, We like to consume a web service. This web service implement a OTA service (this service result Schema is available under http://www.opentravel.org/2005B/OTA_AirAvailRS.xsd). Doesn’t matter witch client proxy generation tool we use (VS 2005 “Add Web Reference”, VS 2005 “Add Service Reference”, WSDL.exe, svcutil.exe, and we also try WSCF – Web Services Contract First Tool from thinktecture and Christian Weyer) doesn’t work. The tools works but the generate wrong code. The schema looks: <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="Schem...

Need help bring over GP 7.0 data to new a GP 8.0 server.
I'm setting up a GP 8.0 Test Server with SQL 2000 SP3a installed. How do I bring my Great Plains 7.0 Data into the GP 8.0 envirnoment? Thanks in advance, The easiest way is the following: On the 7.0 server, detach DYNAMICS. Copy the DYNAMICS mdf and ldf file from the 7.0 to the 8.0 server. On the 8.0 server, attach and browse to the mdf that you copied. On the 7.0 server, attach DYNAMICS. Repeat for all databases. If you also need the sql logins from the 7.0 server, there is a TK on how to do this (uses a table called RecoveryMaster within Dynamics db). If you can go without the s...

Product Key
Is there anyway to retrieve the product key from a SS2005 Standard Edition installation? Can I use Developer Edition media and convert to Standard Edition using the installed product key after testing? I only have one licensed production server. No media no key for Standard Edition. OS on both servers is Windows 2003 sp2. Thanks, Kevin ...

Help with Charts
:confused -------------------------------------------------------------------------------- I work in a Doctors surgery and I'm creating a Spread Sheet for select number of patients to monitor their blood results. I need about 30 charts on each patients sheet, To save me doing eac one seperately I have tryed to copy and paste them in to the nex patients details.... But all the charts refer to the previous patient details. Is there anyway of copying them so that they relate to th sheet they are copied in to? Thanx Alexandra Huckerby (fustrated and tired!! -- ~lexy -----------------...

Excel Formula Help
I need to create a spreadsheet to calculate my weekly payroll. The timecards are punched in a timeclock which prints out the in and out times. for example, in at 7:30 am out at 5:00 pm. It also gives me a running total of the number of hours accumulated. I want to take the total number of hours and minutes and convert that to a decimal. If an employee has 32:35 minutes total time accumulated, then using a formula in an excel spreadsheet, I want to see these hours in decimal format, i.e., 32.58 Anyone know how to help me do this? Thanks! might want to try microsoft.public.excel Mat...

Help with MSN Synchronization-Money 2007
Hello, I have been using money for several years and have just recently upgraded to money 2007. Anyhow, I've noticed since I started using it my information has not updated on MSN money the web site (account transactions,balances, etc.) It also does not show my balance on my checking account (it says N/A) and it used to show it before. Is there some setting that has gotten screwed up somehow? Has anyone else had this problem? Does anyone know if any fixes? I've done everything the knowledge base article said-no results. Any help is appreciated. Thanks, Dan Money comes wit...

email attatchment containing original message
please help every email i receive contains an attatchment which is a notepad page containing the email message too. Stuart, at least 3 of us today have had this problem. I solved this problem on my machine by the simple expedient of rebooting! Before rebooting, I had deleted all cookies, temporary internet files and files in windows/temp, as this was suggested in response to another posting (evidently this helped similar problems in Outlook Express). This latter did nothing, but the reboot did. You might have to do both. Deleting temporary internet files and cookies is done in I...

Help creating two listings from one record
I have extracted "members" from my database. One record will contain LastName and FirstName, and may also contain SpouseName. Because I am creating membership cards in Publisher, I think I probably should actually create another query or ?? that contains one record with either [FirstName LastName] or [SpouseName LastName] Probablyl something like ... If SpouseName greater spaces List SpouseName LastName else next sentence. List FirstName LastName I don't have a clue how to do this in Access. Appreciate all help. Try this -- [FirstName] &a...

Context Sensitive Help
Hi All, I am using winHelp and creating context sensitive help. Every thing is working fine but it is not showing index tab after running ..hlp file.. Secondly when I click to Find tab it show me error Unable to display the Find tab.(177) As I am not much aware of help creation. Help me for good tutorial or suggest solution for this problem Thanx in Advance. Vivek --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.684 / Virus Database: 446 - Release Date: 5/13/2004 Vivek, > I am using winHelp and creating context sensi...

Type Mismatch error on DNS capture... HELP please
Hello, Does anybody can help me on this issue please? I did write a script to capture server primary and secondary DNS through WMI class, it is working properly with many servers in my environment, but for some reason that I don't know, it is stopping during execution at line 23 char 30, Error: Type mismatch, code: 800A000D, for some servers, all servers are in the same domain, with the same architeture, I cannot identify where VB is getting this error, also I've try with IP address and DNS name, same problem. here is the script. Option Explicit Dim DNS1, DNS2, o...

last couple of days mail is in junk e-mail that is safe.
like all @facebookmail.com is going there and I have that on my save senders list. and people I have been getting mail from for months are now showing up in junk email. and it doesn't matter that I tell the junk email to not consider them as junk. I went to junk email properties and switched from high sensitivity to lower and still facebookmail is going to junk... what do I do now? charlie ...

MSScriptControl.ScriptControl help
Her is my powershell code: $vbsCode = Get-Content "c:\scripts\CreateMailbox.vbs" $vbs = New-Object -ComObject MSScriptControl.ScriptControl $vbs.language = "VBScript" $vbs.addCode([string]::Join("'n",$vbsCode)) #$vbs | Get-Member $vbs.CreateMailbox() Here is my vbscript code: Function CreateMailbox(Server,User) Dim oExchsrv Dim sDispName oExchsrv = Server sDispName = User Set oUser = GetObject ("LDAP://cn=" & sDispName & ",ou=New Accounts,dc=stormontvail,dc=org") WScript.Echo sSamAcct & " mailbox cr...

Drop Down Box Help
Hi I am new to using Excel more interactively. I am able to create drop down box so a user can choose a day of the week for example. But I want to be able to populate other cells in the column dependin on the choice from the drop down list. e.g. choose Tuesday from a list of days of the week in A2 and have B2 C2 etc filled with related data. Is this at all possible? Thank you in advance -- smcka ----------------------------------------------------------------------- smckay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3582 View this thread: http://www.exc...

Help with formula please #3
Hello, I need a formula that can do the following: -give an average minutes and seconds per phone call This is the data that I will be entering: Calls: 120 Total talk time (hours): 4 Total talk time (minutes): 47 Total talk time (seconds): 52 Thank you Assuming the hours will not be greater than 23. Try this: B1 = 120 B2 = 4 B3 = 47 B4 = 52 =TIME(B2,B3,B4)/B1 Format as m:ss or h:m:ss -- Biff Microsoft Excel MVP "Jimmy H" <spokenwords@gmail.com> wrote in message news:658b55fd-93e6-4eea-b11f-585544d97364@m7g2000prd.googlegroups.com... > Hello, > > I need a...

help needed with repeated copy/paste special
[image: http://home.cogeco.ca/~rbhayana/backload.gif] [image: http://home.cogeco.ca/~rbhayana/mastertable.gif] I ran into a lot of work and am wondering if anyone can help me out. Help would be greatly appreciated because going thru 19272 records for copying and pasting is driving me insane. :mad: These are the 2 files I'm dealing with (see pictures above). Top one is backload file and the one underneath is mastertable. As you can see, FundataKey is common to both files. The values of Fundatakey are not repeated in mastertable however are repeated in the backload file. What...

Hlookup help
I have a spreadsheet with 23 columns and 361 rows. I am looking to see if I can use HLOOKUP to show me the lowest price in columns G-K-P. The other columns have different data in them. Is there a way to use HLOOKUP or something else to do this? Excel 2007 Thanks in advance. Ed Are you looking for the lowest value in G,K and P for a certain item (maybe it is named in A)? We need a bit more to go on! best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Ed S" <southfloridaed@hotmail.com> wrote in message news:B031D5D3-ECF6-4105-8807...

Publisher Users: Looking for Free Fonts?
http://www.searchfreefonts.com/ This was recommended by Lockergnome online. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. Thank you Brian. Even tho I have 923 fonts onstalled on my system, there are times when I just can't find the exact effect I'm looking for. I appreciate links like this. :-) -- JoAnn Paules MVP Microsoft [Publisher] "Brian Kvalheim - [MSFT MVP]" <bkvalheim@publishermvps.com> wrote in message news:uhnPOpuTEHA.3332...

I'm look for a Visitors Log Sheet
I'm look for a Visitors Log Sheet ...

look up a selection of data
Hi I would like to transfer or look up data when keying in a reference number and bring up all the data regarding the ref number Eg Cell A1 = 101, B1 = John and C1 = january Cell A2 = 102, B2 = Mark and C2 = january Cell A3 = 103, B3 = Sam and C3 = February Cell A4 = 101, B4 = John and C4 = March Cell A5 = 101, B5 = John and C5 = June When I key in Ref 101 in a chosen cell (eg.A10) then the display should have all the details Cell A11 = John, B11 = January Cell A12 = John, B12 = March Cell A13 = John, B13 = June I assume A11, A12 and A13 should also have formulas to generate data but I c...

Calculating Dates, PlEASE HELP! THANKS!
Ok, If I want to calculate the difference of a date with the following date in Access: so 3/29/08 in cell A1 and 5/3/08 in cell A2 and return the value in B1 of 35. How do I do this? Thanks! In Excell the formula looks like this: =IF(+A1-A2>0,A1-A2,0) Is this an Access question? If so, then post you table and field names with datatype, sample data, and what you want the results to look like. -- Build a little, test a little. "kritter0021" wrote: > Ok, If I want to calculate the difference of a date with the following date > in Access: ...

need some help with: formatting of x2 dates in 1 cell
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

subtotal
I am using Excel 2003, recently upgraded to service pack 2. I have a macro that opens a delimited text file, I then copy all, paste values and number formats and then run a macro that subtotals a balance by change in every consumer. The problem is that when, upgraded to service pack 2, the last record does not subtotal, all other records do. I have tried copying only the "live" data after the marco opens the text file. Still the same error. I have tried pasting different ways - values, normal paste, paste values and number formats. Same thing happens - the GRAND TOTAL - ends u...

Help: Need to delete duplicates
I have an access table that has over 470,000 rows. I need to delete the duplicates. None of the "append" query ideas will work as it involves copying and pasing the structure, and Access won't allow me to copy 470,000 rows. Does anyone have a suggestion on how to accomplish this? What error do you get when you run the append query? It could be that the file size is nearing the 2 GB limit. First make a backup copy of the database then run a compact and repair. Then try the append query ideas. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bont...

looking for a script to make a Distribution List from a text file
Does anyone have a script for making a new Distribution List from a text file, or CSV file? I created larger dist lists by using adduser. Then, once the group is created, I mail enable it. "BwiseIT" wrote: > Does anyone have a script for making a new Distribution List from a text > file, or CSV file? > > > ...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...