• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

SQL question

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
Moed
Asked:
Moed
  • 10
  • 6
  • 3
1 Solution
 
melmersCommented:
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
 
doobdaveCommented:
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
 
doobdaveCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
melmersCommented:
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
 
MoedAuthor Commented:
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
 
melmersCommented:
you can add some more tests in the conditonal block like this :
IIF(IsNull(Customers.txtLastname) or isEmpty(Customers.txtLastname), ...
0
 
MoedAuthor Commented:
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
 
melmersCommented:
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
 
melmersCommented:
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
 
MoedAuthor Commented:
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
 
doobdaveCommented:
I beg your pardon!  Did not read the Q carefulkly enough and missed the part about MS Access!

Well done Melmers :o)
0
 
melmersCommented:
Do the second IIF condition in parenthes (IsNull([Customers].[txtHerFirst]) Or  IsEmpty([Customers].[txtHerFirst])) then it should run
0
 
MoedAuthor Commented:
You lost me.  I tried parenthes and go error. Would you be a little more specific.  Thanks
0
 
melmersCommented:
please post me your complete SQL Query that you typed in and the querycode you used in VB.NET.
0
 
MoedAuthor Commented:
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
 
melmersCommented:
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
 
melmersCommented:
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
 
MoedAuthor Commented:
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
 
melmersCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 10
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now