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
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.