Oke, this is my sql statement i'm using in my acces project


SeLECT ID_PERS, ACHTERNAAM + ',' + VOORLETTERS + ' ' + TUSSENVOEGSEL AS NAAM FROM dbo.RAR_TBL_PERSONEN WHERE (NOT (VOORLETTERS IS NULL)) ORDER BY ACHTERNAAM + ',' + VOORLETTERS + ' ' + TUSSENVOEGSEL

problem is that the field "TUSSENVOEGSEL" does not always contain a value (so it is NULL). In this case the query gives an empty field as a result. (so not just de fields VOORNAAM and ACHTERNAAM together)

Does anybody know how to fix this problem.

thank you

maybe i did not do the commas and spaces correctly, but this should give you the right idea

i am a little out of SQL but how about this?

SeLECT ID_PERS, ACHTERNAAM + ',' + VOORLETTERS + rtrim(' ' + TUSSENVOEGSEL) AS NAAM FROM dbo.RAR_TBL_PERSONEN WHERE (NOT (VOORLETTERS IS NULL)) ORDER BY ACHTERNAAM + ',' + VOORLETTERS + rtrim(' ' + TUSSENVOEGSEL)

Greets from Amsterdam

namliam, rtrim(' ' + TUSSENVOEGSEL) will still be null when TUSSENVOEGSEL is null, and then the whole expression it is concatenated to will be null again

there's not much you can do except coalesce it to an empty string

whoops, did i say coalesce

When I use this statement, I get an error message, telling me the "isnull" function needs 2 argument.

What's the second argument ?

i think (because i've never done it myself) that if you are running an "access project" then you actually have to use sql server syntax

see the link for coalesce i gave

found the sollution: want to know

SELECT ID_PERS, ACHTERNAAM + RTRIM(' ' + ISNULL(TUSSENVOEGSEL, N'')) + ', ' + VOORNAAM AS NAAM
FROM dbo.RAR_TBL_PERSONEN
WHERE (NOT (VOORLETTERS IS NULL))
ORDER BY NAAM

thank you all for thinking with me