Do you want text such as:
to be treated as identical?
Otherwise, I don't understand the issue here.
You can define a calculation field that uses FIlter to filter out all characters that you don't want to include in this "unique" count and then reference this calculation field with your count ( Distinct function call.
no. Here are two examples, each containing 5 records:
1) My pname field includes: "Home" ; "Office" ; "Office"; "Room-1"; "Bath"
2) My pname field includes: "Home" ; "Office" ; "Office"; "Room1"; "Bath"
My SQL query
ExecuteSQL ("SELECT COUNT (DISTINCT pname) FROM table1", "", "")
The "-" as special character breaks the procedure in example one. This is my problem. :)
This is specifically what I tested and it did not return a ? in my case. Hence my puzzlement over what you are requesting.
I used this query, which seems identical to yours:
"SELECT Count ( DISTINCT pname ) FROM DataTable" ;
"" ; "" )
You can compare your file to my test file: https://dl.dropboxusercontent.com/u/78737945/ExecuteSQLUniqueCountwSpecialCharsTest.fmp12
I had a look at your file and my own function. That's just what I've been doing. No difference at all.
What actually irritates me (a little bit more) isn't this similarity - but the fact that the function suddenly works fine in my own file. I could swear I didn't change anything in the function / calculation field since the last post because I just left it as it was to solve some other issues. Now that I attempted to tackle the problem once again using your example ... it just worked.
Don't ask me what I did or did change because I really am not aware of any specific step.
Possible guesses as to how you might have had a difference:
ExectuteSQL calcs in fields usually need to be unstored. If you don't make it unstored, it may not update correctly.
Pname needs to be a feild of type "text". If it is not that could create issues for this query.