Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    37

    Unanswered: Field with query (with subquery) as recordsource

    Hi All,

    I have two tables (tblDogsatSighting and tblDogs) and a form (frmDogs).
    I want to calculate the first date a Dog has been seen.

    I wrote this query:
    Code:
    SELECT TOP 1 Sightings.Date
    FROM DogsatSighting INNER JOIN Sightings ON DogsatSighting.SightingID = Sightings.SightingID
    ORDER BY Sightings.Date;
    This returns the first date Dogs were sighted.
    If I set it as recordsource of the field ´DateFirstSeen´ in the form frmDogs it returns: #Name? (both Sightings.Date and frmDogs.DateFirstSeen are of the 'short date' type).

    If I add a WHERE statement to the query I get the same results:
    Code:
    SELECT TOP 1 Sightings.Date
    FROM DogsatSighting INNER JOIN Sightings ON DogsatSighting.SightingID = Sightings.SightingID
    WHERE (((DogsatSighting.DogID)=[Me]![DogID]))
    ORDER BY Sightings.Date;
    If I type the query directly in as a recordsource I get the message that it can't handle syntax of the subquery.

    So I've tried to make it a function (not my strongest point ) and came up with this:
    Code:
    DateFirstSeen.RowSource = "Select Top 1 Sightings.Date "& _
                "From DogsatSighting INNER JOIN Sightings ON DogsatSighting.SightingID=Sightings.SightingID "& _
                "WHERE (((DogsatSighting.DogID) = '" & DogID.Value & "' "& _
                "ORDER BY Sightings.Date;
    Which, not suprisingly, didn't work. But it gave no error message. It just did nothing.

    Any help/suggestions on this would be well appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Create a function like this:

    Code:
    Function DateFirstSeen(SightingID As Long) As Date
    
        Dim strSQL As String
        Dim rst As DAO.Recordset ' do not forget to add a reference to DAO
        
        strSQL = "SELECT TOP 1 Sightings.Date " & _
            "FROM DogsatSighting INNER JOIN Sightings ON DogsatSighting." & SightingID & " = Sightings.SightingID " & _
            "ORDER BY Sightings.Date;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
        DateFirstSeen = rst!Date
        rst.Close
        Set rst = Nothing
    
    End Function
    Then in the ControlSource property of the field where you want the date appear type:
    Code:
    =DateFirstSeen([SightingID])
    Alternatively you can also use the DLookUp function to get the result.

    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    37
    Thanks for your help Sinndho, but it gives me the same error: #Name?

    If I use DLookup like:
    Code:
    =DLookUp("[Date]";"DateFirstSeen")
    it gives me: #error?

    BTW I've also tried this in the control source:
    Code:
    =DateFirstSeen([SightingID])
    Any ideas?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops, sorry!

    Code:
    Function DateFirstSeen(SightingID As Long) As Date
    
        Dim strSQL As String
        Dim rst As DAO.Recordset ' do not forget to add a reference to DAO
        
        strSQL = "SELECT TOP 1 Sightings.Date " & _
            "FROM DogsatSighting INNER JOIN Sightings ON DogsatSighting.SightingID = Sightings.SightingID " & _
            "WHERE Sightings.SightingID = " & SightingID & _
            " ORDER BY Sightings.Date;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
        DateFirstSeen = rst!Date
        rst.Close
        Set rst = Nothing
    
    End Function
    DLookUp was not supposed to be used the way you tried.

    Have a nice day!

  5. #5
    Join Date
    Feb 2009
    Posts
    37
    Same result...

    Am I suppose to put the function in a module or can I put it in an action (Before_Update)?
    I've tried both by the way...
    Maybe I'm just missing something here, I've attached my db if somebody wants to have a look.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here you are!

    I did not know enough of the data structure of your database. It works now.

    Have a nice day!
    Attached Files Attached Files

  7. #7
    Join Date
    Feb 2009
    Posts
    37
    Thanks Sinndho!
    Works perfect

Posting Permissions

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