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