Jump to content

Using Date Fields in ExecuteSQL


This topic is 1573 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies
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

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

 

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

  • Newbies

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

  • 5 years later...
  • Newbies
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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.