The issue here is the "SET STRICTDATE TO" setting. Your app must be setting it to 0 somewhere, but your development environment isn't. I generally turn the setting to 0.
To make it work with the default setting of 1, you would need to format your date in the appropriate format (see the help file for more info on strictdate):
LOCAL lcWhere, lcStartDate, lcEndDate
lcWhere = ''
WITH THISFORM.txtStartDate
IF NOT EMPTY(.Value)
lcStartDate = "^" + TRANSFORM(YEAR(.VALUE)) + ;
"-" + TRANSFORM(MONTH(.VALUE)) + ;
"-" + TRANSFORM(DAY(.VALUE))
lcWhere = "AND invdate>={"+lcStartDate+"}"
ENDIF
ENDWITH
WITH THISFORM.txtEndDate
IF NOT EMPTY(.Value)
lcEndDate = "^" + TRANSFORM(YEAR(.VALUE)) + ;
"-" + TRANSFORM(MONTH(.VALUE)) + ;
"-" + TRANSFORM(DAY(.VALUE))
lcWhere = lcWhere + "AND invdate<={"+lcEndDate+"}"
ENDIF
ENDWITH
>This is wierd. When I build my entire app, I don't get the ambiguous date error message - it appears to work correctly. This may tie into the other question I posted about adding a new form to the system making my specify full path names.
>
>>You can set the text box values to {} to signify an empty date (either in the init or on the property page "value" property. Then the data type will be a date (use DTOC to convert back to a string). Then the following code should work:
>>
>>
>>LOCAL lcWhere
>>lcWhere = ''
>>IF NOT EMPTY(THISFORM.txtStartDate.Value)
>> lcWhere = "AND invpost.invdate>={"+DTOC(THISFORM.txtStartDate.Value)+"}"
>>ENDIF
>>
>>IF NOT EMPTY(THISFORM.txtEndDate.Value)
>> lcWhere = lcWhere + " AND invpost.invdate<={"+ ;
>> DTOC(THISFORM.txtEndDate.Value) + "}"
>>ENDIF
>>
>>
>>
>>>I have a form that has optional date range textboxes on it - start date and end date. The data in those fields is text since I could not figure out how to make it a date datatype. I am building a SELECT statement, but since this field is optional, I figure I must build a string for my where clause and then put it in the select. I cannot figure out how to get the where clause to work. Any help is greatly appreciated.
>>>
>>>whereclause = ''
>>>startdate = ALLTRIM(THISFORM.txtStartDate.Value)
>>>enddate = ALLTRIM(THISFORM.txtEndDate.Value)
>>>
>>>IF !EMPTY(startdate)
>>> whereclause = 'and invpost.invdate >= "&startdate"'
>>>ENDIF
>>>
>>>SELECT invpost.invno,invpost.custno,invpost.invdate,invitempost.itemno ;
>>> FROM invpost INNER JOIN invitempost ;
>>> ON invpost.invno = invitempost.invno ;
>>> INTO CURSOR curResults ;
>>> where invpost.custno = '&selectedcustomer' ;
>>> &whereclause ;
>>> ORDER BY invpost.invdate
>>>
>>>
>>>Randy Wessels