Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    8

    Unanswered: Filter for Records

    I have a simple table with projects, some dates and a column "status" showing if the project has finished or not (yes/no type). The projects are identified with nummbers (Project-Nr). in the table it ist possible that one and the same project number has not been finished and is marked with no, but at a later time it has finished and is marked with yes in the column status.

    for the report I need to extract projects that are finished and the ones that are not. but I cannot simply choose the ones that are marked with yes or no, because some projects with the same number are simply both status yes and no.

    the parts of the table :
    Project-Nr Status
    400 No
    401 No
    402 Yes
    403 No
    403 No
    403 Yes
    ....
    Date-at a certain time a project was not finished at a later time point it is finished, that's why I have duplicate entries in Project-Nr

    Project-Nr ist numerical, Status is Yes/No (True/False)

    I need to extract Projects for the report, the user can choose if he wants to see the finished or unfinished projects. The problem is for example with the project number 403, because it was first set to status NOT FINISHED and later the same number is finished, otherwise I could simply just check the status column and extract the finished and the unfinished ones. I cannot do that, because then project number 403 would appear as unfinished and it is finished in the end.

    this ist my code:

    stSQL = "SELECT [Pr-Nr], Status, Max (Termin) FROM Termine GROUP BY [Pr-Nr], Status"
    rsPSB.CursorLocation = adUseClient
    rsPSB.Open stSQL, conCurrent, adOpenStatic, adLockOptimistic, adCmdText

    and later I want to open a report with this data and several conditions

    strConditionLatestDate= "Termine.Termin ORDER BY Termin DESC LIMIT 1"

    DoCmd.OpenReport "internes Audit", acViewPreview, "query", strConditionDatumAb & " And " & strConditionStatus & " And " & strConditionLatestDate

    but the condition for the latest date doesn't work, do you have any idea how to do this..

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, Access does not recognize the LIMIT keyword in SQL. Use SELECT TOP n instead.
    Have a nice day!

Posting Permissions

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