Solved

SQL question

Posted on 2006-11-03
19
151 Views
Last Modified: 2010-04-23
I am using VB.net 2005 with MS Access.  I want to concatenate the name fields as shown in the following sql:

SELECT ContractInformation.txtContractNumber, ContractInformation.txtInstallStatus, ContractInformation.txtScheduleStatus, Customers.txtCustomerID, Customers.txtCompanyName, Customers.txtLastName, Customers.txtHisFirst, Customers.txtHerFirst, Customers.txtLastname +", "+Customers.txtHisFirst+" & "+Customers.txtHerFirst AS FullName
FROM Customers INNER JOIN ContractInformation ON Customers.txtCustomerID = ContractInformation.txtCustomerID
WHERE (((ContractInformation.txtScheduleStatus)="Complete"));

If the last name is null then the contact would be a company so I want to skip the name.  If either txtHisFirst or txtHerFirst is null then I want to remove the appropriate punctuation.  Is this possible or do I need to process the dataset after it's retrieved?  If its possible whats the sql.
0
Comment
Question by:Moed
  • 10
  • 6
  • 3
19 Comments
 
LVL 11

Expert Comment

by:melmers
Comment Utility
You can do it with the following replacement of your Field for the Fullname:

 IIF(IsNull(Customers.txtLastname),Customers.txtCompanyName,Customers.txtLastname +", "+Customers.txtHisFirst+" & "+Customers.txtHerFirst ) AS FullName

IIF is the conditional block, the first parameter is the isNull() function from access to test if a field is Null. The second Parameter is the True condition and the third is the False condition.
0
 
LVL 8

Expert Comment

by:doobdave
Comment Utility
Hi there,

you can do this in SQL.
I'm assuming you are using SLQ Server?

There are two SQL functions which may be of use to you: ISNULL & COALESCE

ISNULL returns the second parameter if the first is null, eg: SELECT ISNULL(colThatCouldBeNull, Replacement)
COALESCE returns the first non-null value amongst its arguments, eg: COALESCE(colThatCouldBeNull1, colThatCouldBeNull2, Replacement). If all the values are NULL it will return NULL.

I'm a bit unclear on what you actually want to SELECT if the last name is null, and what you want to SELECT if either txtHisFirst or txtHerFirst is NULL.
If you could clarify that, then maybe I can give you an exact answer :)

On a side note, I'd suggest you don't prefix database column names with anything as it just makes things confusing.
The naming convention for database objects tends to be different to that used in programming code.

Best Regards,

David
0
 
LVL 8

Expert Comment

by:doobdave
Comment Utility
melmers,

IIF function does not exist in SQL (SQL Server anyway), but as far as I know it is a code (VB) function.

David
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
David,

Moed wrotes that he is using MS Access, and this SQL-Statement runs with MS Access. I have tested it with VB.NET 2005 and MS Access 2003
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
melmers;

I think you've earned the points on this but it appears another issue is that some of the fields contain empty strings.  ""  Is there a way to also check for that?
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
you can add some more tests in the conditonal block like this :
IIF(IsNull(Customers.txtLastname) or isEmpty(Customers.txtLastname), ...
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
I use this sql:

SELECT Customers.txtCompanyName, Customers.txtLastName, Customers.txtHisFirst, Customers.txtHerFirst, IIF(ISNULL(Customers.txtHerFirst) OR ISEMPTY(Customers.txtHerFirst),Customers.txtLastName & ", " & Customers.txtHisFirst, Customers.txtLastName & ", " & Customers.txtHisFirst & " & " & Customers.txtHerFirst) AS FullName
FROM Customers;

If txtHerFirst is null or empty I get output      Smith, John &    else  Smith, John & Jane   What am I missing?
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
I have copied your code, i become the following result
FullName
Smith, John
Smith, John & Jane
Simth,  & Jane

with 3 columns where one of the names is missing. You must
test HisFirst and HerFirst.
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
Here is the Example i think you would have


SELECT Customers.txtCompanyName, Customers.txtLastName, Customers.txtHisFirst, Customers.txtHerFirst,
IIf( ( NOT  ( IsNull([Customers].[txtHerFirst]) Or  IsEmpty([Customers].[txtHerFirst])) and not ( IsNull([Customers].[txtHisFirst]) Or IsEmpty([Customers].[txtHisFirst]) ) )
,[Customers].[txtLastName] & ", " & [Customers].[txtHisFirst] & " & " & [Customers].[txtHerFirst],
IIF(IsNull([Customers].[txtHerFirst]) Or  IsEmpty([Customers].[txtHerFirst]),[Customers].[txtLastName] & ", " & [Customers].[txtHisFirst], [Customers].[txtLastName] & ", " & [Customers].[txtHerFirst] )) AS FullName
FROM Customers;
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 2

Author Comment

by:Moed
Comment Utility
If txtHisFirst is null or empty I get proper output i.e.   Doe, Jane.   If txtHerFirst is null or empty I get   Doe, John &  

It appears the way you have the sql setup txtHisFirst is not being tested for null.  Where do I put that test?

Appreciate the response.  I'll up the points a bit.
0
 
LVL 8

Expert Comment

by:doobdave
Comment Utility
I beg your pardon!  Did not read the Q carefulkly enough and missed the part about MS Access!

Well done Melmers :o)
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
Do the second IIF condition in parenthes (IsNull([Customers].[txtHerFirst]) Or  IsEmpty([Customers].[txtHerFirst])) then it should run
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
You lost me.  I tried parenthes and go error. Would you be a little more specific.  Thanks
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
please post me your complete SQL Query that you typed in and the querycode you used in VB.NET.
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
I used the exact code you show as an example (the 9:40 post) and tested it in access.  I did not put it in my code yet.  As I mentioned if txtHisFirst is blank it works fine but if txtHerFirst is blank I get   LastName, HisFirst &.   I replaced the & with a letter just to test and it really does hit the first IIF statement.  I checked the table and the fields really are null or empty.  Thanks again for the help.    
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
Have you tested that the fields txtHerFirst is really Empty or Null ?
Are there any whitespaces in the field of txtHerFirst ?

I can only test the SQL String on a Access 2003 German Edition which Version
are you using ?
0
 
LVL 11

Accepted Solution

by:
melmers earned 400 total points
Comment Utility
I have add the Trim Command in both IIF Commands to test if there are any
whitespace characters.

SELECT Customers.txtCompanyName, Customers.txtLastName, Customers.txtHisFirst, Customers.txtHerFirst, IIf(
 ( NOT
         (
           IsNull([Customers].[txtHerFirst])
           Or
           IsEmpty([Customers].[txtHerFirst])
          Or
          TRIM( [Customers].[txtHerFirst]) = ""
          ) AND (
   NOT  
         (
           IsNull([Customers].[txtHisFirst])
           Or
           IsEmpty([Customers].[txtHisFirst])
          Or
          TRIM( [Customers].[txtHisFirst]) = ""
          )
   )
 ),
[Customers].[txtLastName] & ", " & [Customers].[txtHisFirst] & " & " & [Customers].[txtHerFirst],
IIF(
     (
     IsNull([Customers].[txtHerFirst])
     Or
     IsEmpty([Customers].[txtHerFirst])
     Or
     TRIM( [Customers].[txtHerFirst]) = ""
    )
,
[Customers].[txtLastName] & ", " & [Customers].[txtHisFirst],
[Customers].[txtLastName] & ", " & [Customers].[txtHerFirst]
)
) AS FullName
FROM Customers;
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
That did it. When I input a new record should I always use trim to get rid of white space before I save the record.  This has been rather lengthy just to find out there was white space.  Is there a programing standard for this type of thing.  

Thanks again for sticking with it.  I really appreciate the help.  I'll give you a chance to reply then award points.
0
 
LVL 11

Expert Comment

by:melmers
Comment Utility
I don't know if there is a standard, i know that often self written import scripts will insert whitespaces when
process csv files.
But i think you can use trim commands for update and insert commands, too. So you can get rid of the problem with whitespaces.

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now