Concatenation in an Access project (1 Viewer)

[karatelung]

Access 2000 project. SQL Server 2000 backend.

here's my problem.

in data source query for a list box, i concatenated first name and last name fields to show up as one. when one of those fields is blank (LastName or FirstName), the spot is blank in the list. i can still click on it, and it looks up the correct data based on the primary key (EmpID). but it's blank in the list box.

how can i get it to show up? never had this problem with regular Access mdb.

here's the SQL statement -- the data source for the list box.

SELECT EmpID, FirstName + ' ' + LastName AS EmpName
FROM tblEmployee
WHERE (CurrentEmp = 1)
ORDER BY FirstName


thank you.

 

[MStef]

Hello karatelung!
Use & instead of +

 

[namliam]

Or use the Nz(Field, "") to replace the null.

Regardz

 

[karatelung]

thanks for your help. it pointed my research in the right direction.

i believe you must use + instead of & in Access projects (.adp). also, access projects do not support the Nz function. i found out that you can use the coalesce function instead which returns the first non-NULL expression from a list of expressions.

i just did this:

Coalesce(LastName, ' ')

thanks again.

richie

 

[Pat Hartman]

FYI, VBA and Jet use the & as the standard concatenation character but both allow the + in some cases. The two have different properties so it is important to review the help entries for & and + to see the differences.

 

[karatelung]

thank you.

i still have trouble using & in Access projects. is it because the data type is nvarchar?

i get this error when trying to concatenate with &.

Invalid data for data type. Operator equals boolean AND, type equals nvarchar.

 

[Pat Hartman]

Access projects don't use Jet syntax so queries would probably allow only the + which I think is standard for SQL Server. However, within a VBA module, both & and + should be available and they should work as described in help.