Gant Chart type display

[June7]

1. CF Backcolor highlight for current week will conflict with milestone highlight. CF works on only textbox and combobox and cannot set border. Could probably use VBA to set textbox border width and color and/or label backcolor. Code would be in Detail Format event. This event executes only for PrintPreview or direct to printer, not ReportView. Or change the week number labels to textboxes and use CF.
Field Value equal to: Format(Date(), "ww")

2. Change the SQL built in VBA to JOIN tables so ProjectStatus field is available and apply criteria.



sSQLp = sSQLp & "FROM tblSchedulePlan INNER JOIN tblProjects ON tblProjects.ProjectID = tblSchedulePlan.ProjectID WHERE ProjStatus=1"

3. So now the legend appears only on the last page? As a reader, I would find that annoying. Given choice, would prefer on first page.

[petro62]

1. CF Backcolor highlight for current week will conflict with milestone highlight. CF works on only textbox and combobox and cannot set border. Could probably use VBA to set textbox border width and color and/or label backcolor. Code would be in Detail Format event. This event executes only for PrintPreview or direct to printer, not ReportView. Or change the week number labels to textboxes and use CF.
Field Value equal to: Format(Date(), "ww")

2. Change the SQL built in VBA to JOIN tables so ProjectStatus field is available and apply criteria.

sSQLp = sSQLp & "FROM tblSchedulePlan INNER JOIN tblProjects ON tblProjects.ProjectID = tblSchedulePlan.ProjectID WHERE ProjStatus=1"

3. So now the legend appears only on the last page? As a reader, I would find that annoying. Given choice, would prefer on first page.

Well this report most likely wont ever be printed and I don't forsee active projects exceeding one page anytime in the near future. If it does I can switch back to the page footer. Thanks for the rest of the information.

[petro62]

1. CF Backcolor highlight for current week will conflict with milestone highlight. CF works on only textbox and combobox and cannot set border. Could probably use VBA to set textbox border width and color and/or label backcolor. Code would be in Detail Format event. This event executes only for PrintPreview or direct to printer, not ReportView. Or change the week number labels to textboxes and use CF.
Field Value equal to: Format(Date(), "ww")

2. Change the SQL built in VBA to JOIN tables so ProjectStatus field is available and apply criteria.

sSQLp = sSQLp & "FROM tblSchedulePlan INNER JOIN tblProjects ON tblProjects.ProjectID = tblSchedulePlan.ProjectID WHERE ProjStatus=1"

3. So now the legend appears only on the last page? As a reader, I would find that annoying. Given choice, would prefer on first page.

Maybe you could help me understand what is taking place. What is shifting the week numbers in the header? I only ask because I was curious if I wanted to add the months above that how I would do it, but for now I am not understanding the code that shifts the week numbers based on the start week.

[June7]

Code behind report is setting ControlSource of textboxes and Caption property of labels. This info is extracted from fieldnames of crosstab query fields.

Building a stable report based on dynamic crosstab is a common topic. Review https://www.fmsinc.com/MicrosoftAcce...ort/index.html

That example does not use VBA behind report. Calculations take place in textboxes.

[petro62]

1. CF Backcolor highlight for current week will conflict with milestone highlight. CF works on only textbox and combobox and cannot set border. Could probably use VBA to set textbox border width and color and/or label backcolor. Code would be in Detail Format event. This event executes only for PrintPreview or direct to printer, not ReportView. Or change the week number labels to textboxes and use CF.
Field Value equal to: Format(Date(), "ww")

2. Change the SQL built in VBA to JOIN tables so ProjectStatus field is available and apply criteria.

sSQLp = sSQLp & "FROM tblSchedulePlan INNER JOIN tblProjects ON tblProjects.ProjectID = tblSchedulePlan.ProjectID WHERE ProjStatus=1"

3. So now the legend appears only on the last page? As a reader, I would find that annoying. Given choice, would prefer on first page.

TRANSFORM Max(tblScheduleMilestonesReportSetup.SchedPlanPHaseID) AS MaxOfSchedFaseInitIDSELECT tblProjects.ProjectID, IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName]) AS ProjName
FROM tblScheduleMilestonesReportSetup LEFT JOIN tblProjects ON tblScheduleMilestonesReportSetup.ProjectID = tblProjects.[ProjectID]
GROUP BY tblProjects.ProjectID, IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName])
ORDER BY tblProjects.ProjectID DESC , IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName])
PIVOT tblScheduleMilestonesReportSetup.SchedWeek;

So in this code for query I want to insert a condition where it only looks at Active or Future projects which are 1 & 4. I assume I would just do it in the query code, but I wasn't sure where.

I see what you put above and I was curious which line I was replacing here:

    sSQLp = "INSERT INTO tblScheduleMilestonesReportSetup (ProjectID, SchedPlanPhaseID, SchedWeek, SchedWeekSeq) "    sSQLp = sSQLp & "SELECT "
    sSQLp = sSQLp & "[ProjectID], "
    'Select SchedPlanPhaseID if SchedPlanPhaseID is "active" in dCurrDate Week (Monday - Sunday)
    sSQLp = sSQLp & "Iif(#" & dCurrDate & "# between [startdate]-Weekday([startdate],2) +1 and [enddate]-Weekday([enddate],2) +7, [SchedPlanPhaseID],null) as Expr2, "
    'Select SchedWeek (subtract one year if Week is Week 52 ???)
    sSQLp = sSQLp & "'" & IIf(Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) = 52, -1, 0) + DatePart("yyyy", dCurrDate, vbMonday, vbFirstFourDays) & Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) & "' AS SchedWeek ,"
    'Select Week sequence
    sSQLp = sSQLp & iWeek & " "
    sSQLp = sSQLp & "FROM tblSchedulePlan "
    'Group the records by Project and - if SchedPlanPhaseID is active in dCurrDate Week - by SchedPlanPhaseID
    'sSQLp = sSQLp & "GROUP BY projectid, Iif(#" & dCurrDate & "# between [startdate] and [enddate], [SchedPlanPhaseID],null)"

For some reason I have one project that does not have any phases in the date selection I put in, but it is still showing on the report.

[June7]

Can easily add static filter criteria to CROSSTAB query. Just open in design view and add ProjStatus field to design grid, set Total line to WHERE and add appropriate parameter on Criteria line. You are saving descriptive text into ProjStatus instead of status ID, so "Active" Or "Future".

The line to replace is the one with "FROM".

I would probably have to see your latest file to further advise.

[petro62]

Can easily add static filter criteria to CROSSTAB query. Just open in design view and add ProjStatus field to design grid, set Total line to WHERE and add appropriate parameter on Criteria line. You are saving descriptive text into ProjStatus instead of status ID, so "Active" Or "Future".

The line to replace is the one with "FROM".

I would probably have to see your latest file to further advise.

I am not sure what I am doing wrong. I am sure this is very basic, but I keep getting an error.

So if in the qryScheduleMilestones I go into design.
I added a field
Field: ProjStatus
Table: tblProjects
Total: Where
Crosstab: not shown
Sort: Blank
Criteria "Active OR Future"

When I try to run the report now it crashes it.

I get the following error message Run-time Error 3070
The Microsoft Access database engine does not recognize '[202251]' as a valid field or expression.

I even tried creating a new query to start fresh, but I kept getting the same result.

[June7]

Your criteria is missing quote marks: "Active" OR "Future"

I tested with old version of your db. It works.

[petro62]

Your criteria is missing quote marks: "Active" OR "Future"

I tested with old version of your db. It works.

That is odd I typed it in the way you did. I just tested again and it still gave me issues, but then I tried changing it from "Active" OR "Future" to "1" OR "4" and it seems to work. I am not sure what the issue was the other times I tried it with both the text or the number. Either way it seems to be working now. Thanks again.

[June7]

So you are actually saving the ID into a text field instead of the descriptive text.

I think I pointed out this issue with your design some time ago. If you save the ID then it should be into a number type field, not text.

[petro62]

So you are actually saving the ID into a text field instead of the descriptive text.

I think I pointed out this issue with your design some time ago. If you save the ID then it should be into a number type field, not text.

Yes you are correct. Everything seems to be working fine now. I will keep working on a side copy of the database to try and clean it up and such, but thanks for all of the help to you and a few others for helping me get this going.

[petro62]

So you are actually saving the ID into a text field instead of the descriptive text.

I think I pointed out this issue with your design some time ago. If you save the ID then it should be into a number type field, not text.

I got another request for this report. So currently I have it built where it only shows me projects that are active and future and fall within the timeframe. I have been asked to also put an option in there to show those along with completed projects.

So I was thinking I would just create 3 check boxes on the report form. chkActive (1), chkComplete (3), chkFuture (4) and then based on which of those boxes are checked the query would filter properly. So I assume this edit would be done on the WHERE line of the code and it would need to include some sort of IIF function correct?

TRANSFORM Max(tblScheduleMilestonesReportSetup.SchedPlanPHaseID) AS MaxOfSchedFaseInitIDSELECT tblProjects.ProjectID, IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName]) AS ProjName
FROM tblScheduleMilestonesReportSetup LEFT JOIN tblProjects ON tblScheduleMilestonesReportSetup.ProjectID = tblProjects.[ProjectID]
WHERE (((tblProjects.ProjStatus)="1" Or (tblProjects.ProjStatus)="4"))
GROUP BY tblProjects.ProjectID, IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName])
ORDER BY tblProjects.ProjectID DESC , IIf([SchedPlanPhaseID]<10,[tblProjects].[ProjName],[tblProjects].[ProjName])
PIVOT tblScheduleMilestonesReportSetup.SchedWeek;

[June7]

Do you want to allow any combination of the 3 status to be filter criteria?

[petro62]

Do you want to allow any combination of the 3 status to be filter criteria?

Yes they should be able to pick any combination.

[June7]

Well, that gets complicated in CROSSTAB.

Review this info on CROSSTAB dynamic parameters http://allenbrowne.com/ser-67.html#Param

And this http://allenbrowne.com/ser-62.html - go to section at end about query dynamic parameters referencing form controls that might not have input.

An alternative is to use VBA DAO methods to modify query objects.