filtering out fields using code in an address block in a report

[Debbie S.]

How do you filter out fields that in some cases may be null (but not in other
cases) for any given address in a label or on a report? Example:

Address number one:
Joe Schmoe, Ph.D.
123 Main Street
City, State Zip

Address number two:
Joe M. Schmoe
456 Main Street
City, State Zip

How do you code it in a label or on a report so that if one address has a
middle initial, or a suffix, or a title, but another doesn't, it will keep
that value in where necessary and leave it blank where there isn't one but
not leave an empty space?

I hope my question makes sense.

Thank you. -Debbie

 

[Duane Hookom]

You can use either the & or + to concatenate fields/expressions in your
control sources like:
=[FirstName] & " " & ([MI] + ". ") & [LastName]
If there is no MI, then the ". " won't display.

If an entire line in the address is Null then you should be able to set its
Can Shrink property to Yes.

 

[Al Campagna]

Debbie
Several ways to do that...
Try...
=FirstName & " " & IIF(IsNull(MInitial),"", MInitial & ". ") & LastName
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Debbie S.]

Thank you. This works well. A follow up question: I have a couple of people
who have the same last name. For some reason, the database appears to be
merging them as one record in the report. Like this:

Joe M. Schmoe
123 Main Street
City, State, Zip

[name of second person with same last name, say for example, Jack L. Schmoe,
is then missing, and some of the detail records for Jack L. Schmoe are
missing but some are not).

How can I fix that? They are two totally separate records, but for some
reason, I think because the last name is the same, the database thinks it is
one person. Or the report does. What can be done about this?

Also, even though in the form I put a > so that the middle initial will
always be a capital letter, for some reason there is one person whose middle
initial prints as lowercase in the report even though it appears as a capital
in the form. Why would it do that and how can I fix it?

Thanks again,
Debbie

You can use either the & or + to concatenate fields/expressions in your
control sources like:
=[FirstName] & " " & ([MI] + ". ") & [LastName]
If there is no MI, then the ". " won't display.

If an entire line in the address is Null then you should be able to set its
Can Shrink property to Yes.

How do you code it in a label or on a report so that if one address has a
middle initial, or a suffix, or a title, but another doesn't, it will keep
that value in where necessary and leave it blank where there isn't one but
not leave an empty space?

=([Title] + " ") & [FirstName] & " " & ([MI] + ". ") & [LastName] & (", " +
[Suffix])

 

[Duane Hookom]

If records seem to be missing in your report, you might have your controls in
a header or footer rather than the detail section. You might also have "Hide
Duplicates" set to Yes for some controls.

Setting the Format property only affects how something is displayed, not how
it is stored. If you want something always stored in uppercase, I would use
code in the after update event of the text box like:
Me.txtMI = UCase(Me.txtMI)

--
Duane Hookom
Microsoft Access MVP

Thank you. This works well. A follow up question: I have a couple of people
who have the same last name. For some reason, the database appears to be
merging them as one record in the report. Like this:

Joe M. Schmoe
123 Main Street
City, State, Zip

[name of second person with same last name, say for example, Jack L. Schmoe,
is then missing, and some of the detail records for Jack L. Schmoe are
missing but some are not).

How can I fix that? They are two totally separate records, but for some
reason, I think because the last name is the same, the database thinks it is
one person. Or the report does. What can be done about this?

Also, even though in the form I put a > so that the middle initial will
always be a capital letter, for some reason there is one person whose middle
initial prints as lowercase in the report even though it appears as a capital
in the form. Why would it do that and how can I fix it?

Thanks again,
Debbie

You can use either the & or + to concatenate fields/expressions in your
control sources like:
=[FirstName] & " " & ([MI] + ". ") & [LastName]
If there is no MI, then the ". " won't display.

If an entire line in the address is Null then you should be able to set its
Can Shrink property to Yes.

How do you code it in a label or on a report so that if one address has a
middle initial, or a suffix, or a title, but another doesn't, it will keep
that value in where necessary and leave it blank where there isn't one but
not leave an empty space?

=([Title] + " ") & [FirstName] & " " & ([MI] + ". ") & [LastName] & (", " +
[Suffix])

 

[Debbie S.]

Thanks! Both problems solved. Have a nice weekend.

If records seem to be missing in your report, you might have your controls in
a header or footer rather than the detail section. You might also have "Hide
Duplicates" set to Yes for some controls.

Setting the Format property only affects how something is displayed, not how
it is stored. If you want something always stored in uppercase, I would use
code in the after update event of the text box like:
Me.txtMI = UCase(Me.txtMI)

--
Duane Hookom
Microsoft Access MVP

Thank you. This works well. A follow up question: I have a couple of people
who have the same last name. For some reason, the database appears to be
merging them as one record in the report. Like this:

Joe M. Schmoe
123 Main Street
City, State, Zip

[name of second person with same last name, say for example, Jack L. Schmoe,
is then missing, and some of the detail records for Jack L. Schmoe are
missing but some are not).

How can I fix that? They are two totally separate records, but for some
reason, I think because the last name is the same, the database thinks it is
one person. Or the report does. What can be done about this?

Also, even though in the form I put a > so that the middle initial will
always be a capital letter, for some reason there is one person whose middle
initial prints as lowercase in the report even though it appears as a capital
in the form. Why would it do that and how can I fix it?

Thanks again,
Debbie

On Thu, 30 Oct 2008 07:24:01 -0700, Duane Hookom

You can use either the & or + to concatenate fields/expressions in your
control sources like:
=[FirstName] & " " & ([MI] + ". ") & [LastName]
If there is no MI, then the ". " won't display.

If an entire line in the address is Null then you should be able to set its
Can Shrink property to Yes.

How do you code it in a label or on a report so that if one address has a
middle initial, or a suffix, or a title, but another doesn't, it will keep
that value in where necessary and leave it blank where there isn't one but
not leave an empty space?

=([Title] + " ") & [FirstName] & " " & ([MI] + ". ") & [LastName] & (", " +
[Suffix])