Newbies tarlschool Posted June 10, 2014 Newbies Share Posted June 10, 2014 I am trying to use a start date and and end date in an executesql calculation and the result keeps coming back with 0. I can get it to work by a direct executesql using the dates, but would like the option to use an already defined field in the layout (dropdown calendar) like this: Let ( [ begin= GetField("Search Start Date" ) ; end = GetField("Search Stop Date" ) ; $query = " select count(*) from "Office" where qualified='Yes' and "Client Flow Sales Agent"='Sales Guy' and "Client Flow Date In" BETWEEN ? AND ? "; $result = ExecuteSQL ( $query ; "" ; "" ; begin ; end) ]; $result ) Does anyone have any recommendations for a better way to do this or how to fix the current way? Any advice will be greatly appreciated! Thanks Link to comment Share on other sites More sharing options...
john renfrew Posted June 10, 2014 Share Posted June 10, 2014 There is a cost to using BETWEEN, two separate items <= and >= will work better Don't COUNT( everything ) just Count ( aField ) Change field names so they don't contain spaces to avoid all the excessive escaping? Let ( $query = "select count( field ) from "Office" where qualified = 'Yes' and "Client Flow Sales Agent" = 'Sales Guy' AND "Client Flow Date In" = ? " ; ExecuteSQL ( $query ; "" ; "" ; Office::Search Start Date ; Office::Search Stop Date ) ) Link to comment Share on other sites More sharing options...
Wim Decorte Posted June 10, 2014 Share Posted June 10, 2014 Change field names so they don't contain spaces to avoid all the excessive escaping? Even better: don't hard code fields in the queries but use one of the many custom functions that can take a real field reference; that way: - escaping happens automatically - if you ever change a TO or field name, the query will not break Link to comment Share on other sites More sharing options...
Newbies tarlschool Posted June 10, 2014 Author Newbies Share Posted June 10, 2014 Thanks guys I will look into that. Also, am I handling that date field properly? I just created a new field named Search Start Date and when I fill in that date by hand in the layout hoping the data will update when I put different dates in.... Link to comment Share on other sites More sharing options...
Newbies JasonMark Posted December 7, 2019 Newbies Share Posted December 7, 2019 On 6/10/2014 at 8:43 AM, Wim Decorte said: Even better: don't hard code fields in the queries but use one of the many custom functions that can take a real field reference; that way: - escaping happens automatically - if you ever change a TO or field name, the query will not break Can you please link to a custom function? Link to comment Share on other sites More sharing options...
Wim Decorte Posted December 8, 2019 Share Posted December 8, 2019 See the custom functions in de demo file here: https://www.soliantconsulting.com/blog/executesql-named-buckets/ Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 1573 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now