Print parameters or default value

[Bruce]

There are two questions here.
I am using Between [Start Date] And [End Date] as a
reprot's parameters. I can get that to print in mmm d,
yyyy format on the report by adding to an unbound text
box: =Format([Start Date],"mmm d"", ""yyyy") & " to " &
Format([End Date],"mmm d"", ""yyyy"). However, one of the
parameter options is to leave the dates blank in order to
return all records. In that case I would like to come up
with the earliest date for which there is a record and the
current date. For instance, if the earliest record is
6/6/03, I would like the report to read "Jun 6, 2003 to
(current date)". The current date is no problem, but the
earliest date has me stumped. Also, I would like the use
to be able to enter a start date, and have the end date be
the current date if the user leaves it blank. Again, this
would be printed on the report.
My experiments so far have shown me that I cannot figure
out how to set conditions, which would be something like:
If((Start Date and End Date are blank),(earliest date) & "
to " & (current date),If((Start Date) contains a value and
(End Date) is blank,(start date) & " to " & (current date),
(start date) & " to " & (end date)). I expect this would
be IIf rather than If (assuming I am even in the right
neighborhood with this approach), but I cannot get it to
work.
Second question. I would like another text box on the
same report to contain a concatenated value: [PartNum1]
& ", " & [PartNum2]...etc. for four part numbers. The
trouble is that if there is only one part number, I still
get the commas. I can sort of imagine nested IIf
statements in an unbound text box: = IIf ([PartNum1]
= "" , "", IIf (([PartNum1]<>"" And [PartNum2]=""),
[PartNum1], IIf (([PartNum1<>"" And [PartNum2]<>""),
[PartNum1] & ", " & [PartNum2], etc. but for one thing I'm
not sure if it would work, for another I don't know if I
need all of those And statements, and for another I am
hoping there is a better way.
Spaces in all expressions in this posting were added for
ease of reading.

 

[Marshall Barton]

There are two questions here.
I am using Between [Start Date] And [End Date] as a
reprot's parameters. I can get that to print in mmm d,
yyyy format on the report by adding to an unbound text
box: =Format([Start Date],"mmm d"", ""yyyy") & " to " &
Format([End Date],"mmm d"", ""yyyy"). However, one of the
parameter options is to leave the dates blank in order to
return all records. In that case I would like to come up
with the earliest date for which there is a record and the
current date. For instance, if the earliest record is
6/6/03, I would like the report to read "Jun 6, 2003 to
(current date)". The current date is no problem, but the
earliest date has me stumped. Also, I would like the use
to be able to enter a start date, and have the end date be
the current date if the user leaves it blank. Again, this
would be printed on the report.
My experiments so far have shown me that I cannot figure
out how to set conditions, which would be something like:
If((Start Date and End Date are blank),(earliest date) & "
to " & (current date),If((Start Date) contains a value and
(End Date) is blank,(start date) & " to " & (current date),
(start date) & " to " & (end date)). I expect this would
be IIf rather than If (assuming I am even in the right
neighborhood with this approach), but I cannot get it to
work.

Not exactly what you asked for, but I think it will do what
you want:

=Format(IIf(IsError([Start Date]), DMin("thedatefield",
"thetable"), [Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

Second question. I would like another text box on the
same report to contain a concatenated value: [PartNum1]
& ", " & [PartNum2]...etc. for four part numbers. The
trouble is that if there is only one part number, I still
get the commas. I can sort of imagine nested IIf
statements in an unbound text box: = IIf ([PartNum1]
= "" , "", IIf (([PartNum1]<>"" And [PartNum2]=""),
[PartNum1], IIf (([PartNum1<>"" And [PartNum2]<>""),
[PartNum1] & ", " & [PartNum2], etc. but for one thing I'm
not sure if it would work, for another I don't know if I
need all of those And statements, and for another I am
hoping there is a better way.

Don't know if this is "better", but give it a try:

=Mid(", " & PartNum1 & ", " & PartNum2 & ", " & PartNum3 &
", " & PartNum4, 3)

 

[Bruce]

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose record I
am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.
On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.
Thanks for you help with this. I am making progress.

-----Original Message-----

There are two questions here.
I am using Between [Start Date] And [End Date] as a
reprot's parameters. I can get that to print in mmm d,
yyyy format on the report by adding to an unbound text
box: =Format([Start Date],"mmm d"", ""yyyy") & " to " &
Format([End Date],"mmm d"", ""yyyy"). However, one of the
parameter options is to leave the dates blank in order to
return all records. In that case I would like to come up
with the earliest date for which there is a record and the
current date. For instance, if the earliest record is
6/6/03, I would like the report to read "Jun 6, 2003 to
(current date)". The current date is no problem, but the
earliest date has me stumped. Also, I would like the use
to be able to enter a start date, and have the end date be
the current date if the user leaves it blank. Again, this
would be printed on the report.
My experiments so far have shown me that I cannot figure
out how to set conditions, which would be something like:
If((Start Date and End Date are blank),(earliest date) & "
to " & (current date),If((Start Date) contains a value and
(End Date) is blank,(start date) & " to " & (current date),
(start date) & " to " & (end date)). I expect this would
be IIf rather than If (assuming I am even in the right
neighborhood with this approach), but I cannot get it to
work.

Not exactly what you asked for, but I think it will do what
you want:

=Format(IIf(IsError([Start Date]), DMin("thedatefield",
"thetable"), [Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

Second question. I would like another text box on the
same report to contain a concatenated value: [PartNum1]
& ", " & [PartNum2]...etc. for four part numbers. The
trouble is that if there is only one part number, I still
get the commas. I can sort of imagine nested IIf
statements in an unbound text box: = IIf ([PartNum1]
= "" , "", IIf (([PartNum1]<>"" And [PartNum2]=""),
[PartNum1], IIf (([PartNum1<>"" And [PartNum2]<>""),
[PartNum1] & ", " & [PartNum2], etc. but for one thing I'm
not sure if it would work, for another I don't know if I
need all of those And statements, and for another I am
hoping there is a better way.

Don't know if this is "better", but give it a try:

=Mid(", " & PartNum1 & ", " & PartNum2 & ", " & PartNum3 &
", " & PartNum4, 3)

 

[Marshall Barton]

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose record I
am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.

Oh, so the date range is in the group header, I thought it
was in the Page header. In the report header or a group
header, you should be able to use:

=Format(IIf(IsError([Start Date]), Min([thedatefield]),
[Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.

Sorry about that. I was thinking the PartNum values were
Null instead of a ZLS, but even then it should have +
instead of & For ZLS values, you were closer than I was.

This should work better:

=Mid(IIf(PartNum1 = "", "", ", " & PartNum1) &
IIf(PartNum2 = "", "", ", " & PartNum2) &
IIf(PartNum3 = "", "", ", " & PartNum3) &
IIf(PartNum4 = "", "", ", " & PartNum4), 3)

Thanks for you help with this. I am making progress.

Don't see how I helped at all, so far, it seems like I've
just wasted your time. I hope this round gets you to a
point where you can say that with feeling ;-)

-----Original Message-----

There are two questions here.
I am using Between [Start Date] And [End Date] as a
reprot's parameters. I can get that to print in mmm d,
yyyy format on the report by adding to an unbound text
box: =Format([Start Date],"mmm d"", ""yyyy") & " to " &
Format([End Date],"mmm d"", ""yyyy"). However, one of the
parameter options is to leave the dates blank in order to
return all records. In that case I would like to come up
with the earliest date for which there is a record and the
current date. For instance, if the earliest record is
6/6/03, I would like the report to read "Jun 6, 2003 to
(current date)". The current date is no problem, but the
earliest date has me stumped. Also, I would like the use
to be able to enter a start date, and have the end date be
the current date if the user leaves it blank. Again, this
would be printed on the report.
My experiments so far have shown me that I cannot figure
out how to set conditions, which would be something like:
If((Start Date and End Date are blank),(earliest date) & "
to " & (current date),If((Start Date) contains a value and
(End Date) is blank,(start date) & " to " & (current date),
(start date) & " to " & (end date)). I expect this would
be IIf rather than If (assuming I am even in the right
neighborhood with this approach), but I cannot get it to
work.

Not exactly what you asked for, but I think it will do what
you want:

=Format(IIf(IsError([Start Date]), DMin("thedatefield",
"thetable"), [Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

Second question. I would like another text box on the
same report to contain a concatenated value: [PartNum1]
& ", " & [PartNum2]...etc. for four part numbers. The
trouble is that if there is only one part number, I still
get the commas. I can sort of imagine nested IIf
statements in an unbound text box: = IIf ([PartNum1]
= "" , "", IIf (([PartNum1]<>"" And [PartNum2]=""),
[PartNum1], IIf (([PartNum1<>"" And [PartNum2]<>""),
[PartNum1] & ", " & [PartNum2], etc. but for one thing I'm
not sure if it would work, for another I don't know if I
need all of those And statements, and for another I am
hoping there is a better way.

Don't know if this is "better", but give it a try:

=Mid(", " & PartNum1 & ", " & PartNum2 & ", " & PartNum3 &
", " & PartNum4, 3)

 

[Bruce]

Sometimes I suspect I put in too much explanation, so
people's eyes glaze over and they can't identify the
actual question. This time I went too far in the other
direction, not providing a crucial bit of information
about the group header. Your original answer suggests a
direction that will be useful in other projects or other
parts of this one. I have learned a lot of things while
trying to learn something else. I did have to use IsNull
rather than IsError. Null values, ZLS, and other ways of
indicating nothing still puzzle me, but the problem is
solved, and the report contains what it should.
On the second part I was having a problem with your
proposed solution. For some reason it was seeing an empty
field as containing a value, and the commas were back. I
flipped the IIf expression, and it worked. These are the
actual field names I am using; also, I removed the ", "
from in front of the first item and changed the 3 to a 1
at the end.

=Mid(IIf([PartNumber2]<>"",[PartNumber2],"") & IIf
([PartNumber3]<>"",", " & [PartNumber3],"") & IIf
([PartNumber4]<>"",", " & [PartNumber4],"") & IIf
([PartNumber5]<>"",", " & [PartNumber5],""),1)

This is much neater than the nested IIf expression I had
imagined originally. It just needed some tinkering for
the particular situation.

Another question now. The expression above sometimes
takes two lines (wrapped in the text box). The text box's
Can Grow property is set to Yes. However, when the text
box grows it pushes everything else down. That is to say,
there is an text box to the right that is the same height,
and a text box directly below that. When the text box, a
gap appears between the adjacent text boxes. Neither one
is growing, which I discovered by putting borders around
them. In some cases this is a desirable feature, and in
other places it is not. Is there a way to lock the
controls in place, or something that would produce that
effect?

-----Original Message-----

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose record I
am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.

Oh, so the date range is in the group header, I thought it
was in the Page header. In the report header or a group
header, you should be able to use:

=Format(IIf(IsError([Start Date]), Min([thedatefield]),
[Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.

Sorry about that. I was thinking the PartNum values were
Null instead of a ZLS, but even then it should have +
instead of & For ZLS values, you were closer than I was.

This should work better:

=Mid(IIf(PartNum1 = "", "", ", " & PartNum1) &
IIf(PartNum2 = "", "", ", " & PartNum2) &
IIf(PartNum3 = "", "", ", " & PartNum3) &
IIf(PartNum4 = "", "", ", " & PartNum4), 3)

Thanks for you help with this. I am making progress.

Don't see how I helped at all, so far, it seems like I've
just wasted your time. I hope this round gets you to a
point where you can say that with feeling ;-)

-----Original Message-----
Bruce wrote:
There are two questions here.
I am using Between [Start Date] And [End Date] as a
reprot's parameters. I can get that to print in mmm d,
yyyy format on the report by adding to an unbound text
box: =Format([Start Date],"mmm d"", ""yyyy") & " to " &
Format([End Date],"mmm d"", ""yyyy"). However, one of the
parameter options is to leave the dates blank in order to
return all records. In that case I would like to come up
with the earliest date for which there is a record and the
current date. For instance, if the earliest record is
6/6/03, I would like the report to read "Jun 6, 2003 to
(current date)". The current date is no problem, but the
earliest date has me stumped. Also, I would like the use
to be able to enter a start date, and have the end

date
be

the current date if the user leaves it blank. Again, this
would be printed on the report.
My experiments so far have shown me that I cannot figure
out how to set conditions, which would be something like:
If((Start Date and End Date are blank),(earliest date) & "
to " & (current date),If((Start Date) contains a value and
(End Date) is blank,(start date) & " to " & (current date),
(start date) & " to " & (end date)). I expect this would
be IIf rather than If (assuming I am even in the right
neighborhood with this approach), but I cannot get it to
work.

Not exactly what you asked for, but I think it will do what
you want:

=Format(IIf(IsError([Start Date]), DMin("thedatefield",
"thetable"), [Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")


Second question. I would like another text box on the
same report to contain a concatenated value: [PartNum1]
& ", " & [PartNum2]...etc. for four part numbers. The
trouble is that if there is only one part number, I still
get the commas. I can sort of imagine nested IIf
statements in an unbound text box: = IIf ([PartNum1]
= "" , "", IIf (([PartNum1]<>"" And [PartNum2]=""),
[PartNum1], IIf (([PartNum1<>"" And [PartNum2]<>""),
[PartNum1] & ", " & [PartNum2], etc. but for one thing I'm
not sure if it would work, for another I don't know if I
need all of those And statements, and for another I am
hoping there is a better way.


Don't know if this is "better", but give it a try:

=Mid(", " & PartNum1 & ", " & PartNum2 & ", " & PartNum3 &
", " & PartNum4, 3)

 

[Marshall Barton]

Sometimes I suspect I put in too much explanation, so
people's eyes glaze over and they can't identify the
actual question. This time I went too far in the other
direction, not providing a crucial bit of information
about the group header. Your original answer suggests a
direction that will be useful in other projects or other
parts of this one. I have learned a lot of things while
trying to learn something else. I did have to use IsNull
rather than IsError. Null values, ZLS, and other ways of
indicating nothing still puzzle me, but the problem is
solved, and the report contains what it should.
On the second part I was having a problem with your
proposed solution. For some reason it was seeing an empty
field as containing a value, and the commas were back. I
flipped the IIf expression, and it worked. These are the
actual field names I am using; also, I removed the ", "
from in front of the first item and changed the 3 to a 1
at the end.

=Mid(IIf([PartNumber2]<>"",[PartNumber2],"") & IIf
([PartNumber3]<>"",", " & [PartNumber3],"") & IIf
([PartNumber4]<>"",", " & [PartNumber4],"") & IIf
([PartNumber5]<>"",", " & [PartNumber5],""),1)

This is much neater than the nested IIf expression I had
imagined originally. It just needed some tinkering for
the particular situation.

I don't see jow that can work. By changing the Mid start
position to 1, you eliminated the purpose of using Mid.

The general idea of what I was suggesting is that the
sequence of IIf functions would return something like:

, p2, p4

and the Mid function is used to get rid of the extra ", " at
the front.

Another question now. The expression above sometimes
takes two lines (wrapped in the text box). The text box's
Can Grow property is set to Yes. However, when the text
box grows it pushes everything else down. That is to say,
there is an text box to the right that is the same height,
and a text box directly below that. When the text box, a
gap appears between the adjacent text boxes. Neither one
is growing, which I discovered by putting borders around
them. In some cases this is a desirable feature, and in
other places it is not. Is there a way to lock the
controls in place, or something that would produce that
effect?

The text box on the right is being pushed down because its
Top is below the bottom of the CanGrow text box on the left.
I think you can get rid of this issue by making the text box
on the left a little taller so that its bottom is below the
top of the one you don't want to move. (You may or may not
want to set the left text box CanShrink property to Yes.)
--
Marsh
MVP [MS Access]

-----Original Message-----

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose record I
am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.

Oh, so the date range is in the group header, I thought it
was in the Page header. In the report header or a group
header, you should be able to use:

=Format(IIf(IsError([Start Date]), Min([thedatefield]),
[Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")

On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.

Sorry about that. I was thinking the PartNum values were
Null instead of a ZLS, but even then it should have +
instead of & For ZLS values, you were closer than I was.

This should work better:

=Mid(IIf(PartNum1 = "", "", ", " & PartNum1) &
IIf(PartNum2 = "", "", ", " & PartNum2) &
IIf(PartNum3 = "", "", ", " & PartNum3) &
IIf(PartNum4 = "", "", ", " & PartNum4), 3)

 

[Bruce]

I was thinking that PartNumber2 will always be filled in
before PartNumber3. In that case I should have just
concatenated the IIf statements without the MID function.
You're right, the way I did it makes no sense, even if it
does work. But then I figured I might as well go back to
the way you suggested, which will work even if somebody
for some reason skips PartNumber2 but fills in PartNumber3.
Thanks for the tip on the second item. I will have to
paly around with the heights a bit, but it looks as if it
will work. And thank you again for the time you have put
into helping me with this stuff.

-----Original Message-----

Sometimes I suspect I put in too much explanation, so
people's eyes glaze over and they can't identify the
actual question. This time I went too far in the other
direction, not providing a crucial bit of information
about the group header. Your original answer suggests a
direction that will be useful in other projects or other
parts of this one. I have learned a lot of things while
trying to learn something else. I did have to use IsNull
rather than IsError. Null values, ZLS, and other ways of
indicating nothing still puzzle me, but the problem is
solved, and the report contains what it should.
On the second part I was having a problem with your
proposed solution. For some reason it was seeing an empty
field as containing a value, and the commas were back. I
flipped the IIf expression, and it worked. These are the
actual field names I am using; also, I removed the ", "
from in front of the first item and changed the 3 to a 1
at the end.

=Mid(IIf([PartNumber2]<>"",[PartNumber2],"") & IIf
([PartNumber3]<>"",", " & [PartNumber3],"") & IIf
([PartNumber4]<>"",", " & [PartNumber4],"") & IIf
([PartNumber5]<>"",", " & [PartNumber5],""),1)

This is much neater than the nested IIf expression I had
imagined originally. It just needed some tinkering for
the particular situation.

I don't see jow that can work. By changing the Mid start
position to 1, you eliminated the purpose of using Mid.

The general idea of what I was suggesting is that the
sequence of IIf functions would return something like:

, p2, p4

and the Mid function is used to get rid of the extra ", " at
the front.

Another question now. The expression above sometimes
takes two lines (wrapped in the text box). The text box's
Can Grow property is set to Yes. However, when the text
box grows it pushes everything else down. That is to say,
there is an text box to the right that is the same height,
and a text box directly below that. When the text box, a
gap appears between the adjacent text boxes. Neither one
is growing, which I discovered by putting borders around
them. In some cases this is a desirable feature, and in
other places it is not. Is there a way to lock the
controls in place, or something that would produce that
effect?

The text box on the right is being pushed down because its
Top is below the bottom of the CanGrow text box on the left.
I think you can get rid of this issue by making the text box
on the left a little taller so that its bottom is below the
top of the one you don't want to move. (You may or may not
want to set the left text box CanShrink property to Yes.)
--
Marsh
MVP [MS Access]

-----Original Message-----
Bruce wrote:

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose

record
I

am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.

Oh, so the date range is in the group header, I thought it
was in the Page header. In the report header or a group
header, you should be able to use:

=Format(IIf(IsError([Start Date]), Min([thedatefield]),
[Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")



On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.

Sorry about that. I was thinking the PartNum values were
Null instead of a ZLS, but even then it should have +
instead of & For ZLS values, you were closer than I was.

This should work better:

=Mid(IIf(PartNum1 = "", "", ", " & PartNum1) &
IIf(PartNum2 = "", "", ", " & PartNum2) &
IIf(PartNum3 = "", "", ", " & PartNum3) &
IIf(PartNum4 = "", "", ", " & PartNum4), 3)

.

 

[Marshall Barton]

You're welcome and good luck. The can grow, can shrink
stuff can be pretty subtle, but I've found it to be
consistent, at least within its rather obscure set of rules.
--
Marsh
MVP [MS Access]

I was thinking that PartNumber2 will always be filled in
before PartNumber3. In that case I should have just
concatenated the IIf statements without the MID function.
You're right, the way I did it makes no sense, even if it
does work. But then I figured I might as well go back to
the way you suggested, which will work even if somebody
for some reason skips PartNumber2 but fills in PartNumber3.
Thanks for the tip on the second item. I will have to
paly around with the heights a bit, but it looks as if it
will work. And thank you again for the time you have put
into helping me with this stuff.

-----Original Message-----

Sometimes I suspect I put in too much explanation, so
people's eyes glaze over and they can't identify the
actual question. This time I went too far in the other
direction, not providing a crucial bit of information
about the group header. Your original answer suggests a
direction that will be useful in other projects or other
parts of this one. I have learned a lot of things while
trying to learn something else. I did have to use IsNull
rather than IsError. Null values, ZLS, and other ways of
indicating nothing still puzzle me, but the problem is
solved, and the report contains what it should.
On the second part I was having a problem with your
proposed solution. For some reason it was seeing an empty
field as containing a value, and the commas were back. I
flipped the IIf expression, and it worked. These are the
actual field names I am using; also, I removed the ", "
from in front of the first item and changed the 3 to a 1
at the end.

=Mid(IIf([PartNumber2]<>"",[PartNumber2],"") & IIf
([PartNumber3]<>"",", " & [PartNumber3],"") & IIf
([PartNumber4]<>"",", " & [PartNumber4],"") & IIf
([PartNumber5]<>"",", " & [PartNumber5],""),1)

This is much neater than the nested IIf expression I had
imagined originally. It just needed some tinkering for
the particular situation.

I don't see how that can work. By changing the Mid start
position to 1, you eliminated the purpose of using Mid.

The general idea of what I was suggesting is that the
sequence of IIf functions would return something like:

, p2, p4

and the Mid function is used to get rid of the extra ", "
at the front.

Another question now. The expression above sometimes
takes two lines (wrapped in the text box). The text box's
Can Grow property is set to Yes. However, when the text
box grows it pushes everything else down. That is to say,
there is an text box to the right that is the same height,
and a text box directly below that. When the text box, a
gap appears between the adjacent text boxes. Neither one
is growing, which I discovered by putting borders around
them. In some cases this is a desirable feature, and in
other places it is not. Is there a way to lock the
controls in place, or something that would produce that
effect?

The text box on the right is being pushed down because its
Top is below the bottom of the CanGrow text box on the left.
I think you can get rid of this issue by making the text box
on the left a little taller so that its bottom is below the
top of the one you don't want to move. (You may or may not
want to set the left text box CanShrink property to Yes.)

-----Original Message-----
Bruce wrote:

The first expression almost does what I would like. I
left something out of the question. The report is based
on a query that draws from three tables. I am grouping
the report by employee name (be the EmployeeID field,
actually), so that each employee's record is on a
separate
page, with their name on the top. The expression you
provided returns the earliest date from the whole table
(i.e. the earliest date for all employees), but what I
need is the earliest date for the employee whose record
I
am viewing; in other words, the earliest date in the
group. I tried substituting the name of the query, but
got #Error in the text box.

Oh, so the date range is in the group header, I thought it
was in the Page header. In the report header or a group
header, you should be able to use:

=Format(IIf(IsError([Start Date]), Min([thedatefield]),
[Start Date]), "mmm d"", ""yyyy") & " to " &
Format(IIf(IsError([End Date]), Date(), [End Date]), "mmm
d"", ""yyyy")



On the second part of my original question, your
expression leaves me all three commas in every case,
including no values in the fields.

Sorry about that. I was thinking the PartNum values were
Null instead of a ZLS, but even then it should have +
instead of & For ZLS values, you were closer than I was.

This should work better:

=Mid(IIf(PartNum1 = "", "", ", " & PartNum1) &
IIf(PartNum2 = "", "", ", " & PartNum2) &
IIf(PartNum3 = "", "", ", " & PartNum3) &
IIf(PartNum4 = "", "", ", " & PartNum4), 3)