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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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:

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.
--
Marsh
MVP [MS Access]


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?
Click to expand...
Click to expand...
 
confusedPM said:
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:

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])
Click to expand...


Try this:
IIf([Summary]=0," ", "") & [Name]
 
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.

Marshall Barton said:
confusedPM said:
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
Click to expand...


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])
Click to expand...


Try this:
IIf([Summary]=0," ", "") & [Name]
Click to expand...
 
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.
--
Marsh
MVP [MS Access]


I tried that but no luck. The records with "0" for a summary task do not
show up at all.

confusedPM said:
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])
Click to expand...
Click to expand...
Marshall Barton said:
Try this:
IIf([Summary]=0," ", "") & [Name]
Click to expand...
Click to expand...
 
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.

Marshall Barton said:
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.
--
Marsh
MVP [MS Access]


I tried that but no luck. The records with "0" for a summary task do not
show up at all.

confusedPM wrote:

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])
Click to expand...
Marshall Barton said:
Try this:
IIf([Summary]=0," ", "") & [Name]
Click to expand...
Click to expand...
Click to expand...
 
confusedPM said:
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?
Click to expand...


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.
 
Back
Top