Sql Syntax Help

[charlest]

I need some help with executesql statements.

 

I am trying to compare a date in the Contracts table with a date stored in a global field in the BidR table.

 

I want to use executesql to get the date from the contracts table.

 

My statement is:

 

   ExecuteSql ( "SELECT estimatedawarddate_dt FROM contracts WHERE contracts.contractid_t = bidr.contractid_t";"";"")

 

The field "contractid_t" has the same value for both tables.  The returned value is"?" when the statement is executed.

 

Can anyone help with the syntax?

 

Also, how would I compare the date returned by sql with a filemaker date?

 

Thanks.

[Kris M]

what is the field name of the global field in the

Bidr table?

[eos]

My statement is:

 

ExecuteSql ( "SELECT estimatedawarddate_dt FROM contracts WHERE contracts.contractid_t = bidr.contractid_t";"";"")

 

Try

ExecuteSQL ( " 
  SELECT estimatedawarddate_dt 
  FROM contracts 
  WHERE contractid_t = ? 
  " ; "" ; "" ; bidr::contractid_t // this must be a field reference in FileMaker format, since it's a parameter of ExecuteSQL() 
)

Use the optional parameters, and the function will take care of any necessary type conversions, e.g. matching FM and SQL date formats, or implicitly quoting values when you query a text field.

[charlest]

eos,

 

Thanks for your reply, your example helped me understand how to use an optional parameter, but I still get the same result..."?". 

 

I double checked the field data types, that they have actual dates in the fields, and the spelling of the table and field names.  It all seems syntactically correct.

 

I am not sure what else to check or do.

[Wim Decorte]

"?" means a syntax error

 

Look into using a tool like this one http://www.modularfilemaker.org/module/query-builder/

that will help you uncover the syntax error

[beverly]

Wrap in EvaluationError() to see more details (error #)