Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2008
    Posts
    25

    Unanswered: get MAX date while using function

    Hello all

    Ho can I ensure I get the most reccent date from the TBL_Call_Staus_History table? Here is a part of my SQL:

    SELECT .....[other fields here]... Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy") AS [Closed Date],
    FROM ........

    The TBL_Call_Staus_History holds many records and is joined to the main table. For each record in the main table there may be many records in the TBL_Call_Staus_History table. I thought I could wrap a MAX function round it like:

    MAX(Format([TBL_Call_Staus_History.CloseDate],"dd/mm/yyyy")) AS [Closed Date]

    However Access which I am resticted to for this issue is saying 'You tried to execute a query that does not include the specified expression FORMAT([Date Time], "dd/mm/yyyy") as part of an aggregate function.

    TYIA

  2. #2
    Join Date
    Dec 2008
    Posts
    25
    mmmmm my post was very unclear I think. Can i try again....

    I have a very strabge problem. If I run this query (which has been stripped back for this post):

    SELECT Max(TBL_Call_Staus_History.CLoseDate) AS Expr1
    FROM (TBL_Support_Call
    LEFT JOIN TBL_FollowUp ON TBL_Support_Call.ref=TBL_FollowUp.[Original Call Ref])
    LEFT JOIN TBL_Call_Staus_History ON TBL_Support_Call.ref=TBL_Call_Staus_History.refID

    I get the highest date from the table, no issue here. However when I add fields, lets say the TBL_Support_Call.Ref EG:

    SELECT TBL_Support_Call.Ref, Max(TBL_Call_Staus_History.CLoseDate) AS Expr1
    FROM (TBL_Support_Call
    LEFT JOIN TBL_FollowUp ON TBL_Support_Call.ref=TBL_FollowUp.[Original Call Ref])
    LEFT JOIN TBL_Call_Staus_History ON TBL_Support_Call.ref=TBL_Call_Staus_History.refID

    I get a 'You tried to execute a query that does not include the specified expression'Ref' as a part of an aggregate function'

    This query is being run in Access 2003. Why would I get this?

    TYIA

  3. #3
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151
    SELECT Max(lastchanged) AS Expr1
    FROM tblContacts;

    The above brings back the maximum date from this table


    SELECT contactid,Max(lastchanged) AS Expr1
    FROM tblContacts;

    This one causes your same error

    SELECT contactid,Max(lastchanged) AS Expr1
    FROM tblContacts
    group by contactid;

    This one brings back the maximum date for each contactid

    The max function is an inherant 'grouping' clause. When used alone the query will work fine. When you add additional fields then you need a group by and that will probably throw off what you're trying to determine.

    HTH

    Stu
    --If its free, take it for what its worth!

Posting Permissions

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