>Hi:
>
>I am trying to utilize macro substitution to build the "where" clause of a select statement in parts. This is based on user input. For example, I will have 3 check boxes and a command button. The user will select which ever check boxes are appropriate and then click the command button. If chk_1 is selected, then part of the where clause of the select statement will be inputted. If not, then it is not inputted. And this goes for the other two check boxes as well. Therefore, I will be left with any combination of up to 3 where statements in the select statement.
>
>My problem is that I cannot figure out how to determine which will be the last statement in the where part of the select statement. If I guess wrong on what the user will input (of course I won't be guessing, but just as an example), and the last statement in the "where" clause has the "and;" part, than I get an error. If the second to last statement does not have an "and;" part, then I get a "missing operand" error.
>
>Does anyone have any suggestions on how to determine which part of the select statement the variable will be in (last or not last)? Thanks.
>
>Paul

Paul,

You only add an AND to the WHERE clause if it is not empty

I would use one WhereClause instead of 3

local WhereClause

WhereClause = ''

if( chk_1 )
    WhereClause = WhereClause + iif(empty(WhereClause), '', ' AND ') + '( check1 )'
endif
if( chk_2 )
    WhereClause = WhereClause + iif(empty(WhereClause), '', ' AND ') + '( check2 )'
endif
if( chk_3 )
    WhereClause = WhereClause + iif(empty(WhereClause), '', ' AND ') + '( check3 )'
endif

if( !empty(WhereClause) )
    WhereClause = 'where ' + WhereClause
endif

select a, b, c from d &WhereClause