>>>
>
>Snip
>
>I hope this answers most of your questions!!
>
>Bob -
>
>WOW! Thanks so much for taking the time to answer. You provided a lot of information for me to digest. It sounds like you have to account for even more complexities than I had thought of.
>
>I like the idea of a Report.dbf for some of the details, but started realizing I was going to have a big mess in trying to account for all of the WHERE options. I didn't want to create the entire SQL on the fly, so one of the ideas I'm trying today is using an existing form I have for data entry for the main table for which I'd like to allow the user to make choices. I have a query by class whose result is a long string and hope to pass this back to my existing SQL.
>One snafu I'm trying to figure out is how to allow for their selection (e.g. WHERE &csomewhereclause or if they don't want to limit, how to replace the where with a wildcard. There is a tantilizing tip in the SQL help which has to do with using a so-called 'ESCAPE operator' for the join condition to use wildcard characters. I thought this might be a way to go, but can't get it to work.
>
>Sylvia

What I sometimes do in building a where clause is set up some variables

m.cwhere = "WHERE a.code = b.code AND "
m.cfrom = "FROM table1 a, table2 b, "

this assumes I have at least one join, if not cwhere might only hold the value "WHERE "

Then I parse out memory variables such that if they selected a city, for instance I might add

m.cwhere = m.cwhere + "a.city = c.city AND "
m.cfrom = m.cfrom + "city c, "

And just keep doing this. at the end I do

IF LEN(cwhere) = 5 && no where data
cwhere = ''
ELSE
m.cwhere = LEFT(m.cwhere, LEN(m.cwhere) - 4) && remove the last "AND "
ENDIF

m.cfrom = LEFT(m.cfrom, LEN(m.cfrom) - 2) && remove the last ", "


SELECT * ;
&cfrom ;
&cwhere ;
INTO CURSOR QUERY

This creates very flexible statements.