asked on

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.

melmers

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.

doobdave

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

doobdave

melmers,

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

David

melmers

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

Moe DeShong

ASKER

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?

melmers

you can add some more tests in the conditonal block like this :
IIF(IsNull(Customers.txtLastname) or isEmpty(Customers.txtLastname), ...

Moe DeShong

ASKER

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?

melmers

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.

melmers

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;

Moe DeShong

ASKER

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.

doobdave

I beg your pardon! Did not read the Q carefulkly enough and missed the part about MS Access!

Well done Melmers :o)

melmers

Do the second IIF condition in parenthes (IsNull([Customers].[txtHerFirst]) Or IsEmpty([Customers].[txtHerFirst])) then it should run

Moe DeShong

ASKER

You lost me. I tried parenthes and go error. Would you be a little more specific. Thanks

melmers

please post me your complete SQL Query that you typed in and the querycode you used in VB.NET.

Moe DeShong

ASKER

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.

melmers

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 ?

ASKER CERTIFIED SOLUTION

melmers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Moe DeShong

ASKER

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.

melmers

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.