How to create Flexible Access report

[icccapital]

I have two areas on my report where it would be nice if it were flexible.
One is if there is a second address or not. So if a customer does have a
second address (ie suite, etc) I would want that to show up under the first
address portion, but if they don't I would prefer not to have a big space so
that area should compress and compress the whole report. The other portion
of the report is along the same lines, but would involve about 5 lines that
would show up or not show up. I am capable with vba, but haven't done much
in access. Any suggestions or direction you can give is helpful, thank you.

 

[RBear3]

One way if to build your address in one textbox and include carriage
returns. You can set the text box's CAN GROW to true. It will then expand
to fit the data. In the textbox, you'd have something like...

=[FirstName] & " " & [LastName] & chr(13) & chr(10) & [Address1] & chr(13) &
chr(10) & ([Address2] + chr(13) + chr(10)) & [City] & ", " & [State] & " " &
[ZIP]


The "+" combined with the Address2 line will cause the carriage return to be
included only if there is something in Address2.

 

[Carl Rapson]

I have two areas on my report where it would be nice if it were flexible.
One is if there is a second address or not. So if a customer does have a
second address (ie suite, etc) I would want that to show up under the
first
address portion, but if they don't I would prefer not to have a big space
so
that area should compress and compress the whole report. The other
portion
of the report is along the same lines, but would involve about 5 lines
that
would show up or not show up. I am capable with vba, but haven't done
much
in access. Any suggestions or direction you can give is helpful, thank
you.

I think you could do that by making the control height really small and
setting its Can Grow property to Yes. As long as there no other controls in
line with this one (horizontally) it should grow vertically and "push" the
rest of the report down. You shouldn't need any VBA.

Carl Rapson

 

[icccapital]

Both of those are very good suggestions RBear and Carl. If I were to combine
these two responses and create a textbox like Rbear suggests and just make it
very small, but can grow like Carl suggests, would that logically move all
the other controls downward to fit itself? Is it possible to do this with
expresssions as well? For example I have some fields that should only show
up if certain characteristics are true, ie one number is greater than another
or it is greater than 0. Something like that, so it is a tier where the
first would definitely show up on the page, but 2-5 might not depending on
the criteria. Could I put the expression in &(expr)& and have it sometimes
show and expand? Thanks again.

One way if to build your address in one textbox and include carriage
returns. You can set the text box's CAN GROW to true. It will then expand
to fit the data. In the textbox, you'd have something like...

=[FirstName] & " " & [LastName] & chr(13) & chr(10) & [Address1] & chr(13) &
chr(10) & ([Address2] + chr(13) + chr(10)) & [City] & ", " & [State] & " " &
[ZIP]


The "+" combined with the Address2 line will cause the carriage return to be
included only if there is something in Address2.

--
Hope that helps!

RBear3
..

I have two areas on my report where it would be nice if it were flexible.
One is if there is a second address or not. So if a customer does have a
second address (ie suite, etc) I would want that to show up under the
first
address portion, but if they don't I would prefer not to have a big space
so
that area should compress and compress the whole report. The other
portion
of the report is along the same lines, but would involve about 5 lines
that
would show up or not show up. I am capable with vba, but haven't done
much
in access. Any suggestions or direction you can give is helpful, thank
you.

 

[RBear3]

I thought I did suggest setting it to grow in my response.

Yes, all the controls below would move down.

Yes, you can set items to shrink if there is no data in them (or set them to
one pixel high and set them to GROW).

--
Hope that helps!

RBear3
..

Both of those are very good suggestions RBear and Carl. If I were to
combine
these two responses and create a textbox like Rbear suggests and just make
it
very small, but can grow like Carl suggests, would that logically move all
the other controls downward to fit itself? Is it possible to do this with
expresssions as well? For example I have some fields that should only
show
up if certain characteristics are true, ie one number is greater than
another
or it is greater than 0. Something like that, so it is a tier where the
first would definitely show up on the page, but 2-5 might not depending on
the criteria. Could I put the expression in &(expr)& and have it
sometimes
show and expand? Thanks again.

One way if to build your address in one textbox and include carriage
returns. You can set the text box's CAN GROW to true. It will then
expand
to fit the data. In the textbox, you'd have something like...

=[FirstName] & " " & [LastName] & chr(13) & chr(10) & [Address1] &
chr(13) &
chr(10) & ([Address2] + chr(13) + chr(10)) & [City] & ", " & [State] & "
" &
[ZIP]


The "+" combined with the Address2 line will cause the carriage return to
be
included only if there is something in Address2.

--
Hope that helps!

RBear3
..

I have two areas on my report where it would be nice if it were
flexible.
One is if there is a second address or not. So if a customer does have
a
second address (ie suite, etc) I would want that to show up under the
first
address portion, but if they don't I would prefer not to have a big
space
so
that area should compress and compress the whole report. The other
portion
of the report is along the same lines, but would involve about 5 lines
that
would show up or not show up. I am capable with vba, but haven't done
much
in access. Any suggestions or direction you can give is helpful, thank
you.

 

[icccapital]

Sorry, you are correct you did say that. I think this is my last question,
but can expressions in access be conditional? So for example, what I need it
to do is I have a value (call it x) and I have a tier (call it y) and a rate
(r), so if x > y, then (y - x) * r should be displayed, otherwise I don't
want anything to show at all. I think I could create a query to show or not
if this occurs, but would that then work int he report. Or what is your
suggestion for the best way to go about calculating this number and getting
it to a table and or report? So I guess that is several questions, but any
ideas to start the ball rolling would be great. thanks again.

I thought I did suggest setting it to grow in my response.

Yes, all the controls below would move down.

Yes, you can set items to shrink if there is no data in them (or set them to
one pixel high and set them to GROW).

--
Hope that helps!

RBear3
..

Both of those are very good suggestions RBear and Carl. If I were to
combine
these two responses and create a textbox like Rbear suggests and just make
it
very small, but can grow like Carl suggests, would that logically move all
the other controls downward to fit itself? Is it possible to do this with
expresssions as well? For example I have some fields that should only
show
up if certain characteristics are true, ie one number is greater than
another
or it is greater than 0. Something like that, so it is a tier where the
first would definitely show up on the page, but 2-5 might not depending on
the criteria. Could I put the expression in &(expr)& and have it
sometimes
show and expand? Thanks again.

One way if to build your address in one textbox and include carriage
returns. You can set the text box's CAN GROW to true. It will then
expand
to fit the data. In the textbox, you'd have something like...

=[FirstName] & " " & [LastName] & chr(13) & chr(10) & [Address1] &
chr(13) &
chr(10) & ([Address2] + chr(13) + chr(10)) & [City] & ", " & [State] & "
" &
[ZIP]


The "+" combined with the Address2 line will cause the carriage return to
be
included only if there is something in Address2.

--
Hope that helps!

RBear3
..

I have two areas on my report where it would be nice if it were
flexible.
One is if there is a second address or not. So if a customer does have
a
second address (ie suite, etc) I would want that to show up under the
first
address portion, but if they don't I would prefer not to have a big
space
so
that area should compress and compress the whole report. The other
portion
of the report is along the same lines, but would involve about 5 lines
that
would show up or not show up. I am capable with vba, but haven't done
much
in access. Any suggestions or direction you can give is helpful, thank
you.

 

[RBear3]

Just build an unbound text box and include your formula. I believe it would
be...

=IIF([x] > [y], ([y[-[x])*[r],)



--
Hope that helps!

RBear3
..

Sorry, you are correct you did say that. I think this is my last
question,
but can expressions in access be conditional? So for example, what I need
it
to do is I have a value (call it x) and I have a tier (call it y) and a
rate
(r), so if x > y, then (y - x) * r should be displayed, otherwise I don't
want anything to show at all. I think I could create a query to show or
not
if this occurs, but would that then work int he report. Or what is your
suggestion for the best way to go about calculating this number and
getting
it to a table and or report? So I guess that is several questions, but
any
ideas to start the ball rolling would be great. thanks again.

I thought I did suggest setting it to grow in my response.

Yes, all the controls below would move down.

Yes, you can set items to shrink if there is no data in them (or set them
to
one pixel high and set them to GROW).

--
Hope that helps!

RBear3
..

Both of those are very good suggestions RBear and Carl. If I were to
combine
these two responses and create a textbox like Rbear suggests and just
make
it
very small, but can grow like Carl suggests, would that logically move
all
the other controls downward to fit itself? Is it possible to do this
with
expresssions as well? For example I have some fields that should only
show
up if certain characteristics are true, ie one number is greater than
another
or it is greater than 0. Something like that, so it is a tier where
the
first would definitely show up on the page, but 2-5 might not depending
on
the criteria. Could I put the expression in &(expr)& and have it
sometimes
show and expand? Thanks again.

:

One way if to build your address in one textbox and include carriage
returns. You can set the text box's CAN GROW to true. It will then
expand
to fit the data. In the textbox, you'd have something like...

=[FirstName] & " " & [LastName] & chr(13) & chr(10) & [Address1] &
chr(13) &
chr(10) & ([Address2] + chr(13) + chr(10)) & [City] & ", " & [State] &
"
" &
[ZIP]


The "+" combined with the Address2 line will cause the carriage return
to
be
included only if there is something in Address2.

--
Hope that helps!

RBear3
..

I have two areas on my report where it would be nice if it were
flexible.
One is if there is a second address or not. So if a customer does
have
a
second address (ie suite, etc) I would want that to show up under
the
first
address portion, but if they don't I would prefer not to have a big
space
so
that area should compress and compress the whole report. The other
portion
of the report is along the same lines, but would involve about 5
lines
that
would show up or not show up. I am capable with vba, but haven't
done
much
in access. Any suggestions or direction you can give is helpful,
thank
you.