Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: Code to pull most recent report ID from table

    I need help, I'm trying to create a code in VBA to pull the most recently run report ID from a table in the current database. The purpose is to use that report ID in an XML post header. The table name is "ReportIDLog_UnshippedOrder".

    Here's a sample of what it looks like:

    Code:
    ReportID        DateRan
    2608674044      8/17/2009 6:39:31 PM
    2608963538      8/17/2009 9:39:21 PM
    In this case I'd want it to return the report ID 2608963538 because it was run the most recently. This seems like it would not be too difficult but I'm clueless on it. Any help would be appreciated. Thanks! Joshua

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In terms of SQL:

    Get the most recently run record
    Code:
    SELECT Max(dateran)
    FROM   your_table
    Join this back to your_table to get the extra details
    Code:
    SELECT a.reportid
    FROM   your_table As a
     INNER
      JOIN (
            SELECT Max(dateran) As dateran
            FROM   your_table
           ) As b
        ON b.dateran = a.dateran
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    How would I submit that in VBA? I'm trying to avoid creating 2 separate queries and looking up a value in the second one.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    DMax() .
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George's code is a single query. He was demonstrating how he logically built up his SQL by first getting the highest date and then linking that back to the table.

    You would use this SQL like you would any other time when using SQL in VBA to get a value. Have you used SQL from VBA before?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    DMax() .
    Not in a single operation though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Yes, minimally, but never to pull a record to use as a variable.

    I tried putting a query into the following but it returned an error saying it was not a SQL statement.

    Code:
    Dim strSQL As String
    strSQL = "SELECT a.ReportID FROM ReportIDLog_UnshippedOrder As a INNER JOIN ( SELECT Max(DateRan) As DateRan FROM ReportIDLog_UnshippedOrder ) As b ON b.DateRan = a.DateRan"
    DoCmd****nSQL strSQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so waht happens when you examine the sql
    put a watch / break point or just msgbox(strSQL)
    make certain what you think you are sending to the SQL engine is what you actually are sending. thats always a good first step

    when posting here please break up your SQL into multiple lines
    Code:
    strSQL = "SELECT a.ReportID"
    strSQL = " FROM ReportIDLog_UnshippedOrder"
    strSQL = " As a INNER JOIN ( SELECT Max(DateRan) As DateRan FROM ReportIDLog_UnshippedOrder )"
    strSQL = " As b ON b.DateRan = a.DateRan"
    why?
    it makes it easier to read
    makes the SQL easier on the eye, especially when being looked at by people who didn't write the original code.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I went a little different route by using the dmax function in an SQL statement. However, I'm still not experiencing any success. I get run time error 3129: Invalid SQL statement; expected 'DELETE','INSERT','PROCEDURE','SELECT','UPDATE'. Here's what I put in VBA. Can anyone tell me why it is not able to run this SQL statement.

    Code:
    Public Function Test()
        
    Dim strSQL As String
    
    strSQL = "SELECT ReportIDLog_UnshippedOrder.ReportID, ReportIDLog_UnshippedOrder.DateRan "
    strSQL = " FROM ReportIDLog_UnshippedOrder "
    strSQL = " GROUP BY ReportIDLog_UnshippedOrder.ReportID, ReportIDLog_UnshippedOrder.DateRan "
    strSQL = " HAVING (((ReportIDLog_UnshippedOrder.DateRan)=DMax('DateRan','ReportIDLog_UnshippedOrder'))); "
    
    DoCmd****nSQL strSQL
    
    MsgBox strSQL
    
    End Function

  10. #10
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    The above is trying "RunSQL" I have no clue why it is showing "****" when I post. I'm getting really frustrated with this, I thought this seemed pretty easy but I feel like I've tried just about everything.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the **** is triggered by anti spam measures that are built in to vBulletin

    have you looked at the SQL posted by GVee?
    do you think the SQL you are rpoducing is identical to that
    have you tried to use GVee's SQL before branching out on your own

    it may well be that GVee is posting some SQL that doesn't work inside Access/JET. so you may need to look up the JOIN syntax in the help files or possibly the net.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's a filter for all [dot]ru domains due to flagrant abuse. We're exploring options for keeping the filter without hamstringing access code, but no promises.


    You need to concatenate those strings:

    Code:
    Public Function Test()
        
    Dim strSQL As String
    
    strSQL = "SELECT ReportIDLog_UnshippedOrder.ReportID, ReportIDLog_UnshippedOrder.DateRan "
    strSQL = strSQL & " FROM ReportIDLog_UnshippedOrder "
    strSQL = strSQL & " GROUP BY ReportIDLog_UnshippedOrder.ReportID, ReportIDLog_UnshippedOrder.DateRan "
    strSQL = strSQL & " HAVING (((ReportIDLog_UnshippedOrder.DateRan)=DMax('DateRan','ReportIDLog_UnshippedOrder'))); "
    
    DoCmd****nSQL strSQL
    
    MsgBox strSQL
    
    End Function
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Thanks for the help, I went yet another route and finally got it to work. Did a DLookup with the criteria being DMax of DateRan = DateRan. MsgBox returns the correct report ID. Thanks again, Joshua

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •