>>>>
>>
>>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.

Thanks Bob -

I stumbled on the where = '' in playing around, and it does work. Your select statement is almost exactly what I have, except I have only the fields I'm using instead of the *. (I read someplace that it's faster to just get the fields you need; however, I don't know at what point you trade flexibility for speed.) Anyway, you're right, this kind of statement is very flexible. I almost have my reporting form working now; I'm using the Report.dbf approach and have the various selection possibilities (including print options). It's been a real job pulling this together, but I think it's going to be worth it.

Sylvia