My mistake, I forgot to type the create view clause....


Jim
>>I'm trying to create a view based on a string and using the Execscript function, but it's not working and I don't know why. The error message is Syntax Error on the execscript function. Please take a look...
>>
>>

>>	LOCAL lcScript, lcStartDate, lcEndDate
>>	IF USED("deliveries")
>>		SELECT deliveries
>>		USE
>>		DROP VIEW deliveries
>>	ENDIF
>>	lcStartDate = ALLTRIM(STR(YEAR(Session.startdate))) + "-" + ALLTRIM(STR(MONTH(Session.startdate))) + "-" + ALLTRIM(STR(DAY(Session.startdate))) + ' 00:00:00'
>>	lcEndDate = ALLTRIM(STR(YEAR(Session.EndDate))) + "-" + ALLTRIM(STR(MONTH(Session.EndDate))) + "-" + ALLTRIM(STR(DAY(Session.EndDate))) + ' 00:00:00'
>>	lcScript = "SELECT * FROM Deliveries WHERE Deliveries.deliverydate >= cast('" + ALLTRIM(lcstartdate) + "' as datetime) AND deliveries.deliverydate <= cast('" + ALLTRIM(lcenddate) + "' as datetime)"
>>	EXECSCRIPT(lcScript)
>>	IF !USED("deliveries")
>>		USE deliveries IN 0
>>	ENDIF
>>

>
>Jim,
>Good it errored (cast in script was the cause I think).
>This sounds to be a preparation for SQL server. Instead of dropping, recreating (no recreation code there but I think you intended it) create a parametric view in the first place:
>
>

>create sql view deliveries ;
>  remote connection myConnection ;
>  as ;
>  select * FROM Deliveries ;
>  WHERE Deliveries.deliverydate between ?ldStartDate and ?ldEndDate
>

>
>ldStartDate = Session.startdate
>ldEndDate = Session.enddate
>use myDbc!deliveries in 0
>
>ldStartDate = date()-1000
>ldEndDate = date()
>requery('deliveries')
>
>Cetin