Works Great!

Thank you!

>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