Solved

SQL question

Posted on 2006-11-03
19
156 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
  • 3
19 Comments
 
LVL 11

Expert Comment

by:melmers
ID: 17865862
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
ID: 17865894
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
ID: 17865906
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 11

Expert Comment

by:melmers
ID: 17865954
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
ID: 17866161
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
ID: 17866191
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
ID: 17866423
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
ID: 17866545
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
ID: 17866633
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
 
LVL 2

Author Comment

by:Moed
ID: 17866940
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
ID: 17867297
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
ID: 17867620
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
ID: 17868333
You lost me.  I tried parenthes and go error. Would you be a little more specific.  Thanks
0
 
LVL 11

Expert Comment

by:melmers
ID: 17871060
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
ID: 17880671
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
ID: 17880775
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
ID: 17880849
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
ID: 17881375
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
ID: 17881424
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

728 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