By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Displaying null values in query

P: n/a
I have a Customers Table with the following field names.

C_FirstName
C_LastName
C_MiddleI
..
..
..
..
..

When I run the query to give me all of the Customers I get all the
information I want.
Now I'm trying to use the zoom function in the QBE window by doing the
following.

Customer Name: [C_FirstName]+" "+[C_MiddleI]+" "+[C_LastName]

By doing this I only the the results of those records that contain a
Middle Initial.
How can I go about to get the results of all the records even those
without a middle Initial?

Any help will be appreciated.

Ernie

Jul 19 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
In general you want to use & as the concatenation operator (not +) for 2
reasons:
a) It handles Nulls better
b) It does not get confused with numeric addition.

The difference is that:
"A" & Null = "A"
"A" + Null = Null

You can use that to advantage to get rid of multiple spaces:
Customer Name: [C_FirstName] + " " & [C_MiddleI] + " " & [C_LastName]

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ernestb08" <er*******@yahoo.comwrote in message
news:11**********************@m3g2000hsh.googlegro ups.com...
>I have a Customers Table with the following field names.

C_FirstName
C_LastName
C_MiddleI
.
.
.
.
.

When I run the query to give me all of the Customers I get all the
information I want.
Now I'm trying to use the zoom function in the QBE window by doing the
following.

Customer Name: [C_FirstName]+" "+[C_MiddleI]+" "+[C_LastName]

By doing this I only the the results of those records that contain a
Middle Initial.
How can I go about to get the results of all the records even those
without a middle Initial?

Any help will be appreciated.

Ernie
Jul 19 '07 #2

P: n/a
Nulls do not propagate (something + null always equals null). When you use
the "+" operator in concatenation, if any null (no middle initial) is
encountered the entire result will be null.

If you used the & operator to concatenate the string Customer Name:
[C_FirstName] & " " & [C_MiddleI] & " " &[C_LastName]
If the Middle initial were null then an extra space would still be added
between the first and last name which would be undesirable.

By mixing the & and + operators (and wrapping the + strings in parenthesis)
we can eliminate the extra spaces.

Try Customer Name: ([C_FirstName]+" ") & ([C_MiddleI]+" ") & [C_LastName]

If either the first name or middle initial are null then the & operator
concatenates nothing to the string but those fields that have a value are
concatenated.

Len

"ernestb08" <er*******@yahoo.comwrote in message
news:11**********************@m3g2000hsh.googlegro ups.com...
>I have a Customers Table with the following field names.

C_FirstName
C_LastName
C_MiddleI
.
.
.
.
.

When I run the query to give me all of the Customers I get all the
information I want.
Now I'm trying to use the zoom function in the QBE window by doing the
following.

Customer Name: [C_FirstName]+" "+[C_MiddleI]+" "+[C_LastName]

By doing this I only the the results of those records that contain a
Middle Initial.
How can I go about to get the results of all the records even those
without a middle Initial?

Any help will be appreciated.

Ernie

Jul 19 '07 #3

P: n/a
On Jul 19, 8:35 am, "Len Robichaud" <len.robich...@rqwproserv.com>
wrote:
Nulls do not propagate (something + null always equals null). When you use
the "+" operator in concatenation, if any null (no middle initial) is
encountered the entire result will be null.

If you used the & operator to concatenate the string Customer Name:
[C_FirstName] & " " & [C_MiddleI] & " " &[C_LastName]
If the Middle initial were null then an extra space would still be added
between the first and last name which would be undesirable.

By mixing the & and + operators (and wrapping the + strings in parenthesis)
we can eliminate the extra spaces.

Try Customer Name: ([C_FirstName]+" ") & ([C_MiddleI]+" ") & [C_LastName]

If either the first name or middle initial are null then the & operator
concatenates nothing to the string but those fields that have a value are
concatenated.

Len

"ernestb08" <ernest...@yahoo.comwrote in message

news:11**********************@m3g2000hsh.googlegro ups.com...
I have a Customers Table with the following field names.
C_FirstName
C_LastName
C_MiddleI
.
.
.
.
.
When I run the query to give me all of the Customers I get all the
information I want.
Now I'm trying to use the zoom function in the QBE window by doing the
following.
Customer Name: [C_FirstName]+" "+[C_MiddleI]+" "+[C_LastName]
By doing this I only the the results of those records that contain a
Middle Initial.
How can I go about to get the results of all the records even those
without a middle Initial?
Any help will be appreciated.
Ernie- Hide quoted text -

- Show quoted text -
Consider me thick, but I never knew this! Great post!

Jul 19 '07 #4

P: n/a
Jana, you may find this helpful too:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jana" <Ba********@gmail.comwrote in message
news:11**********************@o61g2000hsh.googlegr oups.com...
>
... I never knew this! Great post!
Jul 20 '07 #5

P: n/a
On Jul 19, 6:05 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Jana, you may find this helpful too:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jana" <Bauer.J...@gmail.comwrote in message

news:11**********************@o61g2000hsh.googlegr oups.com...


... I never knew this! Great post!- Hide quoted text -

- Show quoted text -
Thanks, nice tips, but I'd already managed that bit, just didn't know
there was a + concatenator that worked differently than the &. I've
always just used the & and programmed to deal with nulls & spaces
elsewise. This little gem of info will definitely come in handy!

A big fan of this group,
Jana

Jul 20 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.