Concatenating Labels In A Report

[Jim]

Hi,

I have a report that this based on a query. I have placed the following
in the first labels control source

=[Owner_First_Name]+" "+[Owner_Middle_Name]+" "+[Owner_Last_Name]

If there is no data in the Middle Name the First Name and Last Name does
not show. Otherwise all three fields show data.

I can put all three fields up individually and they show what ever
happens to be in the record. I prefer my method because it keeps things
tight. What could cause this or at least what can I do to achieve my goal?

TIA
Jim Mac Millan

 

[Jim]

Out of curiosity why is that the "&" works and the "+" causes the action I
experienced?

TIA again
Jim Mac Millan

 

[Guest]

Hi Jim,

An ampersand (&) concatenates values together, whereas a plus (+) adds them
together. If you add a null to anything, the result is still null. However,
if you concatenate a string to a null, using the ampersand, the result will
be your string value. Try this (untested):

=[Owner_First_Name] & (" "+[Owner_Middle_Name]) & (" " + [Owner_Last_Name])

If there is no middle name, the expression (" "+[Owner_Middle_Name]) should
evaluate to null, leaving you with the owner's first and last names
concantenated together.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi,

I have a report that this based on a query. I have placed the following
in the first labels control source

=[Owner_First_Name]+" "+[Owner_Middle_Name]+" "+[Owner_Last_Name]

If there is no data in the Middle Name the First Name and Last Name does
not show. Otherwise all three fields show data.

I can put all three fields up individually and they show what ever
happens to be in the record. I prefer my method because it keeps things
tight. What could cause this or at least what can I do to achieve my goal?

Out of curiosity why is that the "&" works and the "+" causes the action I
experienced?

TIA
Jim Mac Millan

 

[Jim]

Hi Tom,

That makes sense. I tried it and it does just what you said!

If I may I am using a command button in a form to bring up the report in
question. In the query that the report is based on I have a [?] in the
criteria area to prompt me for the right batch of names. When the report
starts a dialog box pops up asking me to Enter Parameter Value. Above the
entry point is the ?. Can that be changed to say "Enter Batch No."? Or
better yet can I pass the current reports batch No. to the query when I
press the command button? I have some VB experience if that helps.

Thank You
Jim Mac Millan

 

[Guest]

Hi Jim,

First, I'm glad to hear that the concatenation idea worked for you.

How many possible batch numbers are you talking about? If it's not over a
couple hundred or so, you can add a combo box to your form that displays the
batch numbers. This would allow you to pick a single batch number to run a
report. (If you want multi-select capability for >1 batch number, then you'll
need to use a list box with VBA code to build the WHERE portion of a SQL
statement 'on-the-fly'.

With a combo box, you would replace your existing [?] parameter in the query
with a criteria that grabs it's value from the item that is selected in the
combo box. If you have a separate table of batch numbers, then I'd base the
row source for the combo box on this table. Something like this (pkBatchNo is
the primary key field--use the appropriate names for fields in your case):

SELECT pkBatchNo FROM tblBatches
or perhaps this:
SELECT pkBatchNo, BatchDesc FROM tblBatches ORDER BY BatchDesc

The first form would require a column count of 1, whereas the second form
would need a column count of 2. The bound column could be column 1 in both
cases.

In the query, you would add a criteria to the pkBatchNo field that looks
like this:

=[Forms]![NameOfForm]![NameOfComboBox]


You'll probably want to add some code to ensure that the user has selected a
value in the combo box when they click on an OK button to open the report. If
you include a NoData event procedure in the report, to inform the user with a
message box that there are no records, and to cancel opening the report, then
you'll want to trap for error 2501 in the click event procedure for the
command button on the form.

If you send me a private e-mail message with a valid reply-to address, I
will send you a sample. My e-mail address is shown on the bottom of the
contributors page indicated my sig. block. Whatever you do, please do not
post your real e-mail address (or mine) to a newsgroup message. Doing so will
only invite the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom,

That makes sense. I tried it and it does just what you said!

If I may I am using a command button in a form to bring up the report in
question. In the query that the report is based on I have a [?] in the
criteria area to prompt me for the right batch of names. When the report
starts a dialog box pops up asking me to Enter Parameter Value. Above the
entry point is the ?. Can that be changed to say "Enter Batch No."? Or
better yet can I pass the current reports batch No. to the query when I
press the command button? I have some VB experience if that helps.

Thank You
Jim Mac Millan

 

[Rick Brandt]

Out of curiosity why is that the "&" works and the "+" causes the action I
experienced?

It is by design that + propagates Nulls and & does not. Sometimes you want the
behavior of the + and sometimes you want the behavior of &.

 

[Jim]

Tom,

I have a table name "Batch Control" and the field I want to filter on is
"Batch_No". The other table that it is linked to is named "Leads List" I
have a field named "Link_To_Batch_Control" tied to"Batch_No". I do have a
combo box on the form that I use to choose batches from.

If I understand your example correctly I used the following criteria in
the query

SELECT Batch_No FROM Batch Control

When I run it it is telling to enclose the sub query in parentheses. I
don't understand what it is trying to tell me.

Jim

Hi Jim,

First, I'm glad to hear that the concatenation idea worked for you.

How many possible batch numbers are you talking about? If it's not over a
couple hundred or so, you can add a combo box to your form that displays
the
batch numbers. This would allow you to pick a single batch number to run a
report. (If you want multi-select capability for >1 batch number, then
you'll
need to use a list box with VBA code to build the WHERE portion of a SQL
statement 'on-the-fly'.

With a combo box, you would replace your existing [?] parameter in the
query
with a criteria that grabs it's value from the item that is selected in
the
combo box. If you have a separate table of batch numbers, then I'd base
the
row source for the combo box on this table. Something like this (pkBatchNo
is
the primary key field--use the appropriate names for fields in your case):

SELECT pkBatchNo FROM tblBatches
or perhaps this:
SELECT pkBatchNo, BatchDesc FROM tblBatches ORDER BY BatchDesc

The first form would require a column count of 1, whereas the second form
would need a column count of 2. The bound column could be column 1 in both
cases.

In the query, you would add a criteria to the pkBatchNo field that looks
like this:

=[Forms]![NameOfForm]![NameOfComboBox]


You'll probably want to add some code to ensure that the user has selected
a
value in the combo box when they click on an OK button to open the report.
If
you include a NoData event procedure in the report, to inform the user
with a
message box that there are no records, and to cancel opening the report,
then
you'll want to trap for error 2501 in the click event procedure for the
command button on the form.

If you send me a private e-mail message with a valid reply-to address, I
will send you a sample. My e-mail address is shown on the bottom of the
contributors page indicated my sig. block. Whatever you do, please do not
post your real e-mail address (or mine) to a newsgroup message. Doing so
will
only invite the unwanted attention of spammers.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom,

That makes sense. I tried it and it does just what you said!

If I may I am using a command button in a form to bring up the report
in
question. In the query that the report is based on I have a [?] in the
criteria area to prompt me for the right batch of names. When the report
starts a dialog box pops up asking me to Enter Parameter Value. Above the
entry point is the ?. Can that be changed to say "Enter Batch No."? Or
better yet can I pass the current reports batch No. to the query when I
press the command button? I have some VB experience if that helps.

Thank You
Jim Mac Millan

 

[John Vinson]

Out of curiosity why is that the "&" works and the "+" causes the action I
experienced?

Because some Microsoft developer decided the two operators should work
that way.

I'm guessing that it's *usually* appropriate to treat a NULL as if it
were a zero length string in a concatenation, so that

"John" & " " & NULL & " " & "Vinson"

would come out

"John Vinson"

rather than (as you experienced) coming out with nothing at all.

But since both behaviors are sometimes useful, both operators are
available.

John W. Vinson[MVP]

 

[Guest]

Hi Jim,

I meant for you to use the SQL (Structured Query Language) statement as the
Row Source for a combo box on your form, not as the criteria in your query.
The SQL statement would be this:

SELECT Batch_No FROM [Batch Control]

The query criteria would be placed on the Link_To_Batch_Control field in the
query. It would look like this:

=[Forms]![FormName]![ComboBoxName]


where you insert the appropriate names for the form and combo box in the
above criteria. I think this will be much easier for you to see, if you send
me a private message, as I invited you to do earlier. I will send you a nice
tutorial on this.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Tom,

I have a table name "Batch Control" and the field I want to filter on is
"Batch_No". The other table that it is linked to is named "Leads List" I
have a field named "Link_To_Batch_Control" tied to"Batch_No". I do have a
combo box on the form that I use to choose batches from.

If I understand your example correctly I used the following criteria in
the query

SELECT Batch_No FROM Batch Control

When I run it it is telling to enclose the sub query in parentheses. I
don't understand what it is trying to tell me.

Jim