Access Access Filter for Records for Reports

Joined
Oct 20, 2009
Messages
2
Reaction score
0
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..:(
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top