Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Ina View Post
    Micron & Orange
    The code you refer to was given to me by someone else in this thread. I'm not having any luck getting the second value as I'm not yet very familiar with code.
    Could you please be so kind and look at my code and correct it for me, plse. The other value I need is the sum of Males+Females from the same record.
    Very much appreciated!
    I'm attaching a small version of my db.
    There were two bugs in the code I gave you at first in post #4 (I warned you it was untested ), I corrected them in the attached db in post #8. Try the code below instead as it appears to work as you requested.

    *Note that this function will return Null if doesn't find at least 2 or more whelping dates. If it only finds 1 whelping date and you do want to return that date anyway then change rs.RecordCount > 1 to rs.RecordCount > 0

    Code:
    Private Function LookupPrevWhelpingDate(MotherID As Long)
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qry As String
        Dim rslt As Variant
        
        qry = "SELECT TOP 2 WhelpingDate FROM ReproductionT WHERE MotherID = " & MotherID & " ORDER BY WhelpingDate DESC;"
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset(qry)
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveLast
            If rs.RecordCount > 1 Then
               rslt = rs!WhelpingDate
            End If
        End If
        
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        LookupPrevWhelpingDate = rslt
        Exit Function
        
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Function


  2. #17
    Ina is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Location
    Australia
    Posts
    23
    Thank you for that! My date format should be dd/mm/yyyy.

  3. #18
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Perhaps it would be appropriate for you to show the complete structure of the database by attaching an image of the relations window because already from what you have attached you can see several anomalies. guy:
    • there is no Status table (which should be related to the DogT table)
    • bad normalization
    • incorrect management of Field Properties (type Required)
    • etc...

    then explain the activity you carry out from the beginning, that is, when you start managing a dog until eventually it ends.

  4. #19
    Ina is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Location
    Australia
    Posts
    23
    Quote Originally Posted by CarlettoFed View Post
    Perhaps it would be appropriate for you to show the complete structure of the database by attaching an image of the relations window because already from what you have attached you can see several anomalies. guy:
    • there is no Status table (which should be related to the DogT table)
    • bad normalization
    • incorrect management of Field Properties (type Required)
    • etc...

    then explain the activity you carry out from the beginning, that is, when you start managing a dog until eventually it ends.
    My database is too large to send here. When zipped it is just over 5mb.

  5. #20
    Ina is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Location
    Australia
    Posts
    23
    Quote Originally Posted by kd2017 View Post
    There were two bugs in the code I gave you at first in post #4 (I warned you it was untested ), I corrected them in the attached db in post #8. Try the code below instead as it appears to work as you requested.

    *Note that this function will return Null if doesn't find at least 2 or more whelping dates. If it only finds 1 whelping date and you do want to return that date anyway then change rs.RecordCount > 1 to rs.RecordCount > 0

    Code:
    Private Function LookupPrevWhelpingDate(MotherID As Long)
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qry As String
        Dim rslt As Variant
        
        qry = "SELECT TOP 2 WhelpingDate FROM ReproductionT WHERE MotherID = " & MotherID & " ORDER BY WhelpingDate DESC;"
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset(qry)
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveLast
            If rs.RecordCount > 1 Then
               rslt = rs!WhelpingDate
            End If
        End If
        
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        LookupPrevWhelpingDate = rslt
        Exit Function
        
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Function
    Your code is working, thank you. I also need to retrieve the values of fields males & females and add them together. This is also from the 2nd last record.

  6. #21
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My database is too large to send here. When zipped it is just over 5mb.
    Yet you uploaded it to posts 10 and 14?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #22
    Ina is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Location
    Australia
    Posts
    23
    I had to reduce it significantly. I would love someone to look at the whole database. How do I create an image of the relationships?

    Quote Originally Posted by Micron View Post
    Yet you uploaded it to posts 10 and 14?

  8. #23
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    It occurs to me that you don't need my vba to get the 2nd top whelping date... Ajax's post right before my first one is the way to do it.

    ... Sorry for being an idiot. My mind usually goes straight to code before it goes to sql.

  9. #24
    Ina is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Location
    Australia
    Posts
    23
    NewZip.zip
    I actually used your code and it's working perfectly. I'm sending the db again because I have other issues as well.
    The Number of puppies should retrieve it's value from the record with the largest WhelpingDate.
    The Current Litter number should retrieve it's value MLitterCount from the record after the last WhelpingDate.
    Any help is highly appreciated 😊

    Quote Originally Posted by kd2017 View Post
    It occurs to me that you don't need my vba to get the 2nd top whelping date... Ajax's post right before my first one is the way to do it.

    ... Sorry for being an idiot. My mind usually goes straight to code before it goes to sql.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-09-2017, 11:36 AM
  2. Replies: 17
    Last Post: 08-28-2017, 06:25 PM
  3. Replies: 7
    Last Post: 05-23-2012, 02:19 PM
  4. retrieving a record from a table
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 04-25-2012, 01:22 PM
  5. Retrieving records according to criteria
    By jruizmesa in forum Forms
    Replies: 2
    Last Post: 03-24-2012, 11:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums