fn getDate()

I have a statement that gets an error. If I remove the bolded text, it works.
The date in the DB is in 'mm/dd/yy' format. Basically, it should confirm that
the termination date has not yet occured.

(Employee.NAME_FIRST LIKE '%' + ? + '%') AND 
(Employee.SALUTATION LIKE '%' + ? + '%') AND 
(Employee.NAME_LAST LIKE '%' + ? + '%') AND 
(Employee.WORK_PHONE LIKE '%' + ? + '%') AND 
(Employee.PAGER LIKE '%' + ? + '%') AND 
(SalHistory.JOB_LOCATION LIKE '%' + ? + '%') AND 
(Users.USR_FIELD_12 LIKE '%' + ? + '%') AND 
(SalHistory.JOB_SUPERVISOR LIKE '%' + ? + '%') AND
(SalHistory.SEQ = 1) AND
(Employee.TERM_DATE >= {fn getDate()})

Thanks!

What's the error?  Are you using MS SQL or MySql?

 I think the {fn ...} part may be causing the error because it through
"unrecognized" errors at me when I tried on my MS SQL 2005.  Try this
instead:

(Employee.NAME_FIRST LIKE '%' + ? + '%') AND 
(Employee.SALUTATION LIKE '%' + ? + '%') AND 
(Employee.NAME_LAST LIKE '%' + ? + '%') AND 
(Employee.WORK_PHONE LIKE '%' + ? + '%') AND 
(Employee.PAGER LIKE '%' + ? + '%') AND 
(SalHistory.JOB_LOCATION LIKE '%' + ? + '%') AND 
(Users.USR_FIELD_12 LIKE '%' + ? + '%') AND 
(SalHistory.JOB_SUPERVISOR LIKE '%' + ? + '%') AND
(SalHistory.SEQ = 1) AND
(Employee.TERM_DATE >= getDate())

It's actually an Access Database. Sorry that was a vital piece of info...


When I go with getDate(), I get:


Undefined function 'getDate' in expression.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Undefined function
'getDate' in expression.

Source Error:

An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can be
identified using the exception stack trace below.
Stack Trace:



[OleDbException (0x80040e14): Undefined function 'getDate' in expression.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +267
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+192
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +48
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +106
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111
  
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehav
ior behavior) +4
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]
datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +141
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
+137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
  
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArgume
nts arguments) +1770
  
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArg
uments arguments) +74
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
+69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +50
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,
Boolean includeStagesAfterAsyncPoint) +2041
 

When I go with {fn getDate()}, I get:

 
Malformed GUID. in query expression '(Employee.NAME_FIRST LIKE '%' + ? + '%')
AND 
(Employee.SALUTATION LIKE '%' + ? + '%') AND 
(Employee.NAME_LAST LIKE '%' + ? + '%') AND 
(Employee.WORK_PHONE LIKE '%' + ? + '%') AND 
(Employee.PAGER LIKE '%' + ? + '%') AND 
(SalHistory.JOB_LOCATION L'.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Malformed GUID. in query
expression '(Employee.NAME_FIRST LIKE '%' + ? + '%') AND 
(Employee.SALUTATION LIKE '%' + ? + '%') AND 
(Employee.NAME_LAST LIKE '%' + ? + '%') AND 
(Employee.WORK_PHONE LIKE '%' + ? + '%') AND 
(Employee.PAGER LIKE '%' + ? + '%') AND 
(SalHistory.JOB_LOCATION L'.

Source Error:

An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can be
identified using the exception stack trace below.
Stack Trace:



[OleDbException (0x80040e14): Malformed GUID. in query expression
'(Employee.NAME_FIRST LIKE '%' + ? + '%') AND 
(Employee.SALUTATION LIKE '%' + ? + '%') AND 
(Employee.NAME_LAST LIKE '%' + ? + '%') AND 
(Employee.WORK_PHONE LIKE '%' + ? + '%') AND 
(Employee.PAGER LIKE '%' + ? + '%') AND 
(SalHistory.JOB_LOCATION L'.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) +267
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
+192
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,
Object& executeResult) +48
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior
behavior, String method) +106
   System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) +111
  
System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehav
ior behavior) +4
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[]
datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand
command, CommandBehavior behavior) +141
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
+137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
  
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArgume
nts arguments) +1770
  
System.Web.UI.WebControls.AccessDataSourceView.ExecuteSelect(DataSourceSelectArg
uments arguments) +74
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments,
DataSourceViewSelectCallback callback) +17
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
   System.Web.UI.WebControls.GridView.DataBind() +4
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
+69
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.PreRenderRecursiveInternal() +50
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Control.PreRenderRecursiveInternal() +170
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint,
Boolean includeStagesAfterAsyncPoint) +2041

(Employee.TERM_REASON <= Now()) works...

odd

HCBMRDD:odd  

Not really.  GetDate() is Sql Server specific.  Access ofers two built-in
date/time functions: Now() and Date().  Now() returns the current system date
and time, and Date() returns the current date with the time fixed at 12.00
noon.