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

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

TIA again
Jim Mac Millan


> 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
>

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Jim" wrote:

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

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

"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:FC509DDE-C3E1-4B8E-B202-(E-Mail Removed)...
> 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/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
> "Jim" wrote:
>
> 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

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Jim" wrote:

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

"Jim" <(E-Mail Removed)> wrote in message
news:%23W0XM$(E-Mail Removed)...
> 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 &.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

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


"Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
news:02892A42-B5DF-4781-8365-(E-Mail Removed)...
> 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/ex...tributors.html
> http://www.access.qbuilt.com/html/search.html
> __________________________________________
>
> "Jim" wrote:
>
> 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

On Sat, 21 Jan 2006 14:15:26 -0700, "Jim" <(E-Mail Removed)>
wrote:

>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]

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Jim" wrote:

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