Updates to data based on a query (adding spaces before the data)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

confusedPM - 20 Oct 2004 21:45 GMT

I am creating a report based on a query created from a table out of MS project.

The report I'm writing needs to simulate outlining of which is foundin MS
Project. My report needs the following format:

> >>>    SUMMARY TASK
> >>>        PROJECT TASK
> >>>        PROJECT TASK
> >>>        PROJECT TASK

The problem I have is that the NAME(called Project Task above) value has to
appear in both the Summary and Task Field on the report, depending on its
corresponding value in the [Summary] field. In other words, if [Summary]=-1,
then it needs to be in the Summary field on the report, and if the [Summary]
value =0, then it needs to be indented beneath the Summary field.

I have created the following code within my query; however, when I run my
query, I get only the records of which have a "-1" for summary task. The
records with "0" for summary task do not show up when I add the "
" for the spaces/indentation.
The records with "0" for the summary task do show up if I leave out the
spaces. Here's my code:
IIf([Summary]=0," ",[Name]) Or IIf([Summary]=-1,[Name])

Can anyone help me to figure out how to do this? Thanks so much in advance.

> Multiple queries may be needed, but at this point I don't
> see it being required. It's possible that a little code can
> be used to hide or show the task name where needed.
>
> If you would provide a little sample of how you want the
> report's output to look along with a few comments to explain
> the tricky parts, it might help me better understand your
> problem.
> >The problem I have is that the TASK_NAME value has to
> >appear in both the Summary and Task Field on the report,
> >depending on its corresponding value in the
> >TASK_IS_SUMMARY field. In other words, if TASK_IS_SUMMARY
> >=-1, then it needs to be in the Summary field on the
> >report, and if the TASK_IS_SUMMARY value =0, then it
> >needs to be indented beneath the Summary field.
> >
> >I'm thinking that I'm going to have to produce multiple
> >queries, one to pull out the summary tasks, and another
> >to pull out the regular tasks, then a thrid to produce a
> >report.
> >
> >I realize I didn't do a very good job of explaining the
> >output I was after. What would someone need to know to
> >make this clearer?
> >
> >>-----Original Message-----
> >>FlyBoy wrote:
> >>>In Access 2002, I'm writing a report based on a query
> >>>that hits
> >>>two linked MS Project Server 2003 SQL tables,
> >>>dbo_MSP_PROJECTS
> >>>and dbo_MSP_Tasks. They are joined at the PROJ_ID field.
> >>>
> >>>The report I'm writing has to semi-emulate the
> >outlining
> >>>found
> >>>in an MS Project Plan. My report needs to have The
> >>>following format:
> >>>
> >>>PROJECT NAME
> >>>    SUMMARY TASK
> >>>        PROJECT TASK
> >>>        PROJECT TASK
> >>>        PROJECT TASK
> >>>
> >>>I use the following SQL statement to create the query:
> >>>(line breaks used
> >>>to fit in this NG window)
> >>>SELECT dbo_MSP_PROJECTS.PROJ_ID,
> >>>dbo_MSP_PROJECTS.PROJ_NAME,
> >>>dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
> >>>dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
> >>>dbo_MSP_TASKS.TASK_START_DATE,
> >>>dbo_MSP_TASKS.TASK_FINISH_DATE,
> >>>dbo_MSP_TASKS.TASK_WORK
> >>>FROM dbo_MSP_PROJECTS INNER JOIN dbo_MSP_TASKS ON
> >>>dbo_MSP_PROJECTS.PROJ_ID = dbo_MSP_TASKS.PROJ_ID
> >>>GROUP BY dbo_MSP_PROJECTS.PROJ_ID,
> >>>dbo_MSP_PROJECTS.PROJ_NAME,
> >>>dbo_MSP_TASKS.TASK_IS_SUMMARY, dbo_MSP_TASKS.TASK_ID,
> >>>dbo_MSP_TASKS.TASK_NAME, dbo_MSP_TASKS.TASK_PCT_COMP,
> >>>dbo_MSP_TASKS.TASK_START_DATE,
> >>>dbo_MSP_TASKS.TASK_FINISH_DATE,
> >>>dbo_MSP_TASKS.TASK_WORK
> >>>HAVING (((dbo_MSP_PROJECTS.PROJ_ID)=429));
> >>>
> >>>How can I set up my report to Group by Project (that I
> >>>got), then group by
> >>>summary task and insert the task name, then by task?
> >>>
> >>>The field in dbo_MSP_TASKS for summary tasks is
> >>>TASK_IS_SUMMARY, and
> >>>contains either a -1 if the task is a summary or 0 if
> >it
> >>>is not.
> >>
> >Marshall Barton wrote:
> >>As best I can tell, the grouping in your query does
> >nothing
> >>(you do not have any aggregate expressions in the select
> >>field list).
> >>
> >>Unless I'm missing something important, get rid of the
> >>qyery's GROUP BY clause and change HAVING to WHERE.

Reply to this Message

Marshall Barton - 20 Oct 2004 23:29 GMT

>I am creating a report based on a query created from a table out of MS project.
>
[quoted text clipped - 18 lines]
>spaces. Here's my code:
>IIf([Summary]=0," ",[Name]) Or IIf([Summary]=-1,[Name])

Try this:
IIf([Summary]=0," ", "") & [Name]

Signature

Marsh
MVP [MS Access]

Reply to this Message

confusedPM - 21 Oct 2004 15:12 GMT

Hi Marshal,

I tried that but no luck. The records with "0" for a summary task do not
show up at all. Any other ideas? Thanks so much for your help.

> >I am creating a report based on a query created from a table out of MS project.
> >
[quoted text clipped - 21 lines]
> Try this:
> IIf([Summary]=0," ", "") & [Name]

Reply to this Message

Marshall Barton - 21 Oct 2004 17:03 GMT

I guess you have more going on in your query than you've
explained thus far. Either the Summary field doesn't really
have a 0 (maybe Null??) or there is some kind of tricky
criteria. Double check the actual values in the query's
datasheet view (without getting the report involved). If
you still can't figure it out, post a Copy/Paste of the
query's SQL view and I'll see if I can spot something.

Signature

Marsh
MVP [MS Access]

>I tried that but no luck. The records with "0" for a summary task do not
>show up at all.
[quoted text clipped - 24 lines]
>> Try this:
>> IIf([Summary]=0," ", "") & [Name]

Reply to this Message

confusedPM - 21 Oct 2004 17:59 GMT

Got it! The problem was(other than the fact I'm still learning Access), that
I was putting the code on the "Criteria" line of the "Name" column in the
query rather than creating a new column for the expression. So..with that
done..it works great..except...

Now the records which have "0" for the [Summary] field are now indented the
20 spaces that I asked it to indent; however, when the record shows up on the
report is just does what I tell it to do which is add 20 spaces before
showing the text in the [Name] field; however, what I really want it to do is
indent the indent the entire text(even when it wraps around on the report) to
the right 20 spaces. Is there a way to do this?

For example, right now on the report the [Name] field which is not a
[Summary] task looks like this:
Create document as a result of testing/evaluation for the
blah..blah..blah..of the project.

What I need is the entire text of the [Name] to right indent some amount of
spaces like at least 5 or 10 spaces. Have any suggestions on how I may do
this?

thanks so much.

> I guess you have more going on in your query than you've
> explained thus far. Either the Summary field doesn't really
[quoted text clipped - 31 lines]
> >> Try this:
> >> IIf([Summary]=0," ", "") & [Name]

Reply to this Message

Marshall Barton - 21 Oct 2004 20:21 GMT

>Now the records which have "0" for the [Summary] field are now indented the
>20 spaces that I asked it to indent; however, when the record shows up on the
[quoted text clipped - 11 lines]
>spaces like at least 5 or 10 spaces. Have any suggestions on how I may do
>this?

Whole 'nother problem!

To do this, we need to get rid of the mucking about in the
query and use a little code to move the text box to the
desired position.

Add code like this to the Format event procedure of the
section containing the summary text box:

    If Me.txtSummary = 0 Then
        Me.txtName.Left = .5 * 1440        ' 1/2 inch
    Else
        Me.txtName.Left = 1.6 * 1440
    End If

Note that I use txtName as the name of the text box so we
wouldn't get confused between the control and the field it
is displaying. Even more important is that Name is a very
common Access reserved word that will often do it's own
thing instead of what you might intend - you should definite
change the name of the field to something else such as
ProjName.

FYI, the 1440 is the number of twips (Access internal unit
of measurement) per inch. Play around with the .5 and 1.6
until you're happy with the positions.

Signature

Marsh
MVP [MS Access]

Reply to this Message