variable in select statement

[jn03]

Hi all,

In my cmdprint.click event, I'd like to pass in a variable to the select statement to dynamically sort the order of the cursor, so it looks like this:

LPARAMETERS lcorder

lcorder = ALLTRIM(STR(this.Parent.myorder))
&& this.Parent.myorder is a number, such as 1 or 2,3,4...

select * FROM curcustomer ;
order BY (&lcorder) into CURSOR rpcurcust

This works, but if I want to do something like this

LPARAMETERS lcorder

lcorder = ALLTRIM(STR(this.Parent.myorder))

if lcorder='2'
lcorder=lcorder+ ' Desc' && to sort the column in descent
endif

select * FROM curcustomer ;
order BY (&lcorder) into CURSOR rpcurcust

then VFP gives me an error, stating that there are so many parameters that the right parenthesis ) is not recognized.

I can work around by repeating the select statement for each case, but it doesn't look nice. Any suggestions?

Thanks.

 

[rgbean]

Don't use the ()'s - the macro (&) is good enough. i.e.:

select * FROM curcustomer ;
 order BY &lcorder into CURSOR rpcurcust

Rick

 

[Mike Lewis]

Hi Jn,

I think the problem might be related to the fact that your macro (&lcOrder) will have a space in it, just before the DESC.

Why don't you do something like this:

lcorder = ALLTRIM(STR(this.Parent.myorder))

if lcOrder = '2'
select * FROM curcustomer ;
order BY (&lcorder) DESC into CURSOR rpcurcust
else
select * FROM curcustomer ;
order BY (&lcorder) into CURSOR rpcurcust
endif

I can't see why that shouldn't work.

Mike
Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[jn03]

It works now without the (). Thanks, Rick.

Mike, your're right, it didn't work because there was a space in it. Your suggestion was my work-around

Thanks a lot guys.