Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Newest Record Query (Access 2000)

    I want to create a query to find the most recent record, in a table of date stamped records.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    What happens if there is more than one record with the most recent date? Do you want one record only, or all? Or is that impossible?

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    appologies the solution I just posted (now at the bottom of this message) won't work if you put the other fields in (other than the date)

    just put the date stamp field in.
    Create a second query that uses this MaxDate query and your original table, linking on MaxDate->DateStamp, then include all the fields you want. (without totals)
    ---------------------------------------
    Create new query
    Add the table with the dates
    put all field you want the query to return
    from menu: View Totals
    in the Totals row all fields should say "group by" EXCEPT for date:
    change the value in the totals row to MAX

    This query will return the most recent record.
    Jenny

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    Hi Hans
    There is a combination of a serial number and date. A serial number may have a number of observations recorded against it, but each observation will have a unique date stamp. I just want to see the most recent observation.

    Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    You can try a nested query - set the criteria for the date field to the result of a query that returns the most recent date. The SQL for the entire query would look like this:

    SELECT *
    FROM tblSomething
    WHERE DateField=(SELECT Max(DateField) FROM tblSomething)

    Of course, you must replace tblSomething and DateField by the appropriate names.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    You can use the Top 1 command with a query sorted by date. That will give you the last record. Adding a serial number though, means that you need more then 1 return, so you probably need a subquery, do your records have a unique key?

    If so, and you still need help, post your data structure, or attach a zipped copy of the table.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Newest Record Query (Access 2000)

    You might want to include the serial number in that subquery. Otherwise, you'll get the latest date but not necessarily for that serial number.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    Hello
    I'm revisiting this question in another db. This time I want to take the result of my query (which should be one number) and feed it into a new record, creating (you guessed it) my own version of the autonumber field.
    I've placed the following in the default value of the field:
    =(SELECT TOP 1 Max([SeqNumber]+1) AS Incremented
    FROM test
    My current stumbling block, I am receiving the #Name? error in the field in the form
    Suggestions?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    You can't do this in the default value. You have to do it on the form bound to the table.

    If there is no risk of two users entering a new record at the same time, the following simple Before Insert event procedure will do:

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim lngSeqNumber As Long
    lngSeqNumber = Nz(DMax("SeqNumber","Test"),0)+1
    If lngSeqNumber > 99999 Then
    MsgBox "You're out of luck, mate"
    Cancel = True
    Else
    Me.SeqNumber = lngSeqNumber
    End If
    End Sub

    If multiple users will be entering new records, it is safer to keep the last SeqNumber used in a separate table with just one field and one record. Let's say that you name this table tblSeqNumber with a field named SeqNumber.

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim lngSeqNumber As Long
    ' Get most recently used value and increment
    lngSeqNumber = DLookup("SeqNumber","tblSeqNumber") + 1
    ' Check if it is 5 digits
    If lngSeqNumber > 99999 Then
    MsgBox "You're out of luck, mate"
    Cancel = True
    Else
    ' Update value in separate table
    CurrentDb.Execute "UPDATE tblSeqNumber SET SeqNumber = " & lngSeqNumber
    ' And in the form
    Me.SeqNumber = lngSeqNumber
    End If
    End Sub

  10. #10
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    Thanks Hans
    Now, a question related to your second suggestion. Not only am I writing the sequence number to the separate table, but I also want to keep the last transaction date there as well.
    I have tried two things, both of which give me weird dates.
    1)
    Dim CurrDate as Date
    CurrDate = Format(Me.DateField, "yyyy/mmm/dd")
    CurrentDb.Execute "UPDATE SequenceNumber SET LastDate = " & CurrDate
    2)
    Dim strCurrDate as String
    strCurrDate = (Year(Me.DateField)) & "/" & (Month(Me.DateField)) & "/" & (Day(Me.DateField))
    CurrentDb.Execute "UPDATE SequenceNumber SET LastDate = " & strCurrDate
    This last one, places a value of 1900/Jan/08 instead of today's date in the LastDate field.

    Any comments on what I'm doing?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    SQL is rather finicky when it comes to dates. Literal date values must be enclosed in # characters, and the date format MUST be mm/dd/yy (or mm/dd/yyyy). So try

    Dim CurrDate as String
    CurrDate = Format(Me.DateField, "mm/dd/yy")
    CurrentDb.Execute "UPDATE SequenceNumber SET LastDate = #" & CurrDate & "#"

  12. #12
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    Thanks Hans, that did the trick. I think I need an Access reference with better SQL - or maybe just an SQL reference.
    Currently, I've got the Sybex "Mastering Access 2000, Premium Edition" (and the weird posture that comes with dragging that boat anchor around in a shoulder bag). But it isn't all I could wish for. Any book suggestions?

    Cheers

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    I don't know which books are especially strong on SQL, but I'm sure one of the others will have a recommendation.

  14. #14
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Newest Record Query (Access 2000)

    This is what I have so far:
    <pre>
    Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim bytSeqNumber As Byte
    Dim strCurrDate As String
    Dim strPrevDate As String
    Dim strPrevMonth As String
    Dim strCurrentMonth As String
    Dim strCurrentYear As String
    Dim strAlphaMonth As String
    Dim strReleaseNumber As String

    ' Get most recently used value of sequencenumber and increment
    bytSeqNumber = DLookup("SeqNumber", "SequenceNumber")
    ' get the last transaction date
    strPrevDate = DLookup("LastDate", "SequenceNumber")

    ' check if the month has changed since last transaction
    'extract month from previous date
    strPrevMonth = Month(strPrevDate)
    'extract month from current date on form
    strCurrentMonth = Month(Me.DateField)

    ' if it is a new month, reset the sequence number to 1, otherwise increment by 1
    If strPrevMonth = strCurrentMonth Then
    bytSeqNumber = bytSeqNumber + 1
    Else
    bytSeqNumber = 1
    End If

    'format sequence number so that it is always two digits long
    'bytSeqNumber = Format(bytSeqNumber, "00")'this isn't working

    'Convert month to letter form
    Select Case strCurrentMonth
    Case 1
    strAlphaMonth = "A"
    Case 2
    strAlphaMonth = "B"
    Case 3
    strAlphaMonth = "C"
    Case 4
    strAlphaMonth = "D"
    Case 5
    strAlphaMonth = "E"
    Case 6
    strAlphaMonth = "F"
    Case 7
    strAlphaMonth = "G"
    Case 8
    strAlphaMonth = "H"
    Case 9
    strAlphaMonth = "I"
    Case 10
    strAlphaMonth = "J"
    Case 11
    strAlphaMonth = "K"
    Case 12
    strAlphaMonth = "L"
    End Select

    'extract year as 2 digits
    strCurrentYear = Right((Year(Me.DateField)), 2)

    'create releasenumber
    strReleaseNumber = strCurrentYear & strAlphaMonth & bytSeqNumber

    ' set value of release number field
    Me.ReleaseNumber = strReleaseNumber

    ' Update values in separate table
    strCurrDate = Format(Me.DateField, "mm/dd/yy")

    CurrentDb.Execute "UPDATE SequenceNumber SET SeqNumber = " & bytSeqNumber
    CurrentDb.Execute "UPDATE SequenceNumber SET LastDate = #" & strCurrDate & "#"
    End Sub
    </pre>

    You can see, I have changed my strategy. Instead of just an incrementing sequence number, I'm now going with a number formed from the last two digits of the year, a letter representing the month, and the incrementing sequence number. At the beginning of each month, the month letter will change and the sequence will restart at 1.
    Now, what I'd like to do is format the sequence number such that; that it is always 2 digits long even if it is less than 10. I'd also like an error message generated if the sequence number goes past 99.
    I can think of a "long" ways to do this; ie check the value, concatenate a 0 onto the string or give an error message. But is there a shorter, more elegant solution?

    TIA

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Newest Record Query (Access 2000)

    Hi 3cats,

    There is some confusion between string and numeric variables in this code. VBA is reasonably forgiving, but there are some things you can't do.
    <hr>'bytSeqNumber = Format(bytSeqNumber, "00")'this isn't working<hr>
    The Format function returns a string, but since SeqNumber is a numeric variable, the return value is converted into a number again, and the format is lost.

    I have attached modified code. The distinction between text and numeric has been made clearer, the Select Case statement has been replaced by a single expression involving Chr, and a test for bytSeqNumber has been added.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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