Using Date Fields in ExecuteSQL

[tarlschool]

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

[john renfrew]

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 )
)

[Wim Decorte]

 

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

[tarlschool]

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....

[JasonMark]

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? 

[Wim Decorte]

See the custom functions in de demo file here:

https://www.soliantconsulting.com/blog/executesql-named-buckets/