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
5  2280 
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*******@yaho o.comwrote in message
news:11******** **************@ m3g2000hsh.goog legroups.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
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*******@yaho o.comwrote in message
news:11******** **************@ m3g2000hsh.goog legroups.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
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...@yaho o.comwrote in message
news:11******** **************@ m3g2000hsh.goog legroups.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!
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********@gma il.comwrote in message
news:11******** **************@ o61g2000hsh.goo glegroups.com.. .
>
... I never knew this! Great post!
On Jul 19, 6:05 pm, "Allen Browne" <AllenBro...@Se eSig.Invalidwro te:
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...@gma il.comwrote in message
news:11******** **************@ o61g2000hsh.goo glegroups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Morten Gulbrandsen |
last post by:
--------------
SELECT Version()
--------------
+--------------------+
| Version() |
+--------------------+
| 5.0.0-alpha-max-nt |
+--------------------+
1 row in set (0.00 sec)
|
by: harsha |
last post by:
Dear All,
I am using a SQL server database with around 20 columns,all
the columns have numeric values, I want to write an SQL statement
which does the following:
compare each row of the table with all other rows in the table and
return all the rows that have a difference of + or - 0.5 in each
column, for eg if row1 has values 12.2,13.6,11.4,15.7 corresponding
to column1,2,3,4 the sql statement should return all the rows from the...
|
by: Ellen Manning |
last post by:
Using SQL2000. I want to return the # of columns with non-null
values. Here's my query so far:
select
case when Dx1 is not null then 0 else 1 end +
case when Dx2 is not null then 0 else 1 end +
case when Dx3 is not null then 0 else 1 end +
case when Dx4 is not null then 0 else 1 end as DxCount
from tblClerkshipDataClean
where PalmName = @PalmName
|
by: JOEP |
last post by:
What do I need to do to allow an append query to post null values to
records in a field of the destination table? Basically I want to allow
records with null values to post to the table. The append query will
not work unless there are values in the data i am attempting to send.
I want the fields in the destination table to accept null and
populated values. Any help here would be gladly accepted.
thanks
|
by: Edward Koucherian |
last post by:
I'm getting the incorrect display value on my DataGrid column.
The field is numeric (1) in the SQL database table
The Code is
txtCol = New DataGridBoolColumn
txtCol.MappingName = "MyCol"
txtCol.HeaderText = ""
txtCol.Width = 25
txtCol.ReadOnly = False
txtCol.AllowNull = False
| | |
by: Sardaukary |
last post by:
Hello,
>From the following table showing which countries people have been to
Name Countries
Fred France
Fred UK
Bill Germany
Jack USA
|
by: shar7 |
last post by:
Hi, I am new to SQL and I need help to display null value as 0.
Problem description:
The query is to display Branch Code, Branch Location, Account Type Code, account Type Description and Total Current Values of each type of account. The report also needs to show a total of zero if the account type is not currently in use at that branch - ie. all branches should have two entries, one for Savings and one for Term Deposits.
My code works...
|
by: ADezii |
last post by:
Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...
|
by: Gilles Ganault |
last post by:
Hello
I use the prepare/execute combo to generate SQL statements. Is it
possible to actually see what PDO creates with prepare()?
=======
$sql = "INSERT INTO mytable (id, label) VALUES (NULL,?)";
$insert = $dbh->prepare($sql);
//Display SQL before running it
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
