using dialog input to populate query

Question

I am trying to use a user dialog to choose some options, and then use those options in a sql query.  So the dialog box would be something like:

_________________________________________________________
mydialog = Dialog(
	title( "Query" ),
	vlist(
		"Start date",
		startdate = EditText( "m/d/yy" ),
		"End date",
		enddate = EditText( "m/d/yy" ),
		Name1 = Check Box( "Tom", 1 ),
		Name2 = Check Box( "Bill", 1 ),
		Name3 = Check Box( "Mary", 1 ),
		Name4 = Check Box( "Sue", 1 ),

),
	hlist( Button( "OK" ), Button( "Cancel" ),  ),

);

__________________________________________________________

Then I want to use the choices in a sql query something like:

SELECT * from tEmployees WHERE (ColumnName LIKE 'Name1' OR ColumnName LIKE 'Name2' OR ColumnName LIKE 'Name3') etc. etc.

I can make it work if the choices in the dialog are always the same.  What I'm struggling with is adapting it to account for the user just selecting one name vs. all four names.  I've been trying to get a for-loop to work but haven't had much success.  Does anyone have any tips?

Thanks.
Clark

unknown · Answer

Thanks to both of you.  I took some ideas from each of your scripts and got something working.  I appreciate the help.

unknown · Answer

SQL_String6=char(EndDate);

if(
Product == 1,SQL_String7 = "and HTM in (46,47,58,59)",
Product==2, SQL_String7 = "and HTM in (42,43,60,61)",
Product==3, SQL_String7 = "and HTM in (56,57,62,63)");

SQL_Command = SQL_String1 || SQL_String2 || SQL_String3 || SQL_String4 || SQL_String5 || SQL_String6 || SQL_String7;

command = text1 || "\!"" ||  text2 || "\!"" || ",\!"" || SQL_Command || "\!"" ||  ",\!"" || text3 ||"\!")";

dlg = Dialog(
"NOTICE:",
"You are about to start downloading a huge volume of data, this may take time. Please be patient.",
"Do you want ot continue?",
H List( Button( "OK" ), Button( "Cancel" ) )
);

If( dlg["Button"] == -1, Throw( "User cancelled" ) );
eval(parse(command));
---------->

unknown · Answer

text1= "tb=open database(";
text2= "DSN=DB;UID=reynald;PWD=xxx;MODE=SHARE;DBALIAS=DB;";
text3 = "FRBQ";
SQL_string1="select * from table where column in (";

SQL_String2=char(Parmnum);
SQL_string3= ")and storets>=";
SQL_String4=char(StartDate);
SQL_String5="and storets<=";
SQL_String6=char(EndDate);

if(
Product == 1,SQL_String7 = "and HTM in (46,47,58,59)",
Product==2, SQL_String7 = "and HTM in (42,43,60,61)",
Product==3, SQL_String7 = "and HTM in (56,57,62,63)");

SQL_Command = SQL_String1 || SQL_String2 || SQL_String3 || SQL_String4 || SQL_String5 || SQL_String6 || SQL_String7;

command = text1 || "\!"" ||  text2 || "\!"" || ",\!"" || SQL_Command || "\!"" ||  ",\!"" || text3 ||"\!")";

dlg = Dialog(
"NOTICE:",
"You are about to start downloading a huge volume of data, this may take time. Please be patient.",
"Do you want ot continue?",
H List( Button( "OK" ), Button( "Cancel" ) )
);

If( dlg["Button"] == -1, Throw( "User cancelled" ) );
eval(parse(command));
---------->