Group by custom expression

[hbrems]

I'm trying to run a query with a custom built function 'StripNumbers'. This function removes any numbers from a string and returns a string as result.

This works:

SELECT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt

The same function with DISTINCT added returns a data type mismatch error.

SELECT DISTINCT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt

I get the same error with a GROUP BY clause.

SELECT StripNumbers(pt.Code2) AS Program
FROM Primary_Transformants AS pt
GROUP BY StripNumbers(pt.Code2)

Can anyone help me understand where the problem lies so I can work around it?

 

[hbrems]

I've solved my own problem. In case where a field was empty the function would return #error. I can't group by #error. Lesson learned!

So as a solution I could add an nz statement to turn empty values into empty strings. Or I could update my function to return an emtpy string in case something goes wrong.

SELECT DISTINCT StripNumbers(nz(pt.Code2, "")) AS Program
FROM Primary_Transformants AS pt