"refine the search"?
Your SQL query counts all distinct values in the entire table. If you want to selectively count values, you'll need to use a WHERE clause in the query that selects for the records you want to count.
Awesome. That would explain it. So...
ExecuteSQL ("SELECT COUNT (DISTINCT EmployeeID) FROM CallType where date between STARTDATE from Globals and ENDDATE from Globals" ; "" ; "")
Apologies but my SQL is super rusty. Am I missing something? It now displays a "?" again. Do date, STARTDATE and ENDDATE need to be referanced differently?
Thanks in advance.
date is a reserved word in SQL so the SQL engine fails to recognize it as a field name. Put it into your SQL String like this: \"date\". Given how often a field or table occurrence name can trip a syntax error, it's not a bad idea to enclose all of them in quotes like this.
The backslash is the escape character you need to put before quotation mark characters so that FileMaker doesn't interpret the quote as the end of the string.
SeedCodes SQLExplorer is a handy tool you can use to help get the syntax correct. And the SELECT query examples found in the ODBC JDBC Guide that you can access from FileMaker help are accurate examples of the SQL syntax you can use with the ExecuteSQL function.
Wow. SQLExplorer is pretty cool. Provided a good example of what we found previously within a few seconds. Great tool I'm sure I'll use. Unfortunately, I just still can't figure out what is going on with my query.
Before Phil's pointing out of date being a reserved word, I changed the field to be reportDate. I am still getting the "?". Through testing, I simplified my query to see if it worked at all:ExecuteSQL("SELECT COUNT (DISTINCT \"EmployeeID\")FROM CallTypeWHERE \"reportDate\" = '07/01/2013'"; "" ; "")This returns the correct value of 46. I then changed \"reportDate\" = to \"reportDate\" between '07/01/2013' and '07/30/2013'. This also returned a correct value of 95. But once I changed it to: WHERE reportDate between \"STARTDATE\" from Globals and \"ENDDATE\" from GLOBALS, it provides a "?".To add to my difficulty, the layout that I am working with also has a checkmark set (global value) that performs a search on a value called "CallType" in my CallType table.Edit: Found out through testing that Let() variables do not translate to the SQL query.
Use Globals.StartDate and Globals.EndDate to refer to your two fields instead of FROM.
You are awesome with the quick response. Unfortunately, it still does not appear to be calculating correctly. Here is my newest iteration:ExecuteSQL("SELECT COUNT (DISTINCT \"EmployeeID\")FROM CallTypeWHERE \"reportDate\" BETWEEN Globals.STARTDATE and Globals.ENDDATE"; "" ; "")I have confirmed that Start and end dates are populated and display properly as M/D/YYYY. Also confirmed that it populates correctly when viewed in the same layout. Additionally, I confirmed that all dates are set to the date type. Still, though, the only time I see a proper result is when I replace "Globals.Startdate" and "Globals.EndDate" with date values in quotes.I apologize if I'm missing something and appreciate the help. Thanks again.
Are startDate and ENddate fields of type date or are they text? They'll look correct if they are text, but won't evaluate correctly. REportDate also needs to be of type date and als oneeds to have an identifying table occurrence reference.
I'd try this expresson:ExecuteSQL("SELECT COUNT (DISTINCT \"EmployeeID\")FROM CallTypeWHERE Calltype.\"reportDate\" BETWEEN ? and ?"; "" ; "" ; Globals::STARTDATE ; Globals::ENDDATE )
Yuzzir. All fields are of type date. The awesome thing is that adding the fields to the arguments section worked like a charm. Just curious on how this functions. Does the arguments go in the order of the question marks? IE first question mark gets assigned the first argument, second gets second? I was reading up on the ExecuteSQL function and didn't see an example that explains this. Thanks again for your awesome work.
Do the arguments go in the order of the question marks?
I'm relearning SQL myself--used to use it a lot with MS Access and have found that danged ? result when I messed up syntax frustratingly uninformative.
The reason (I think) that you need to use the ? parameters is due to the fact that you don't have the Globals table declared anywhere in your SQL. If it were part of a Join clause, you wouldn't need to use the substitution parameters.
Another thing to keep in mind is that the first parameter in the ExecuteSQL fuction call is simply a string expression. While the examples typically show a literal string for the SQL, any text result that evaluates to a valid SQL query may be used. That opens the door to using other methods to produce a SQL query that reconfigures on the file to produce very different results based on how that text get's produced in the first parameter.