Hi all

I have a query in which I need to use either of two WHERE clauses depending on the parameters passed or no parameter passed to the query (via Remote View in VFP)

The present query is this:
SELECT tCartridge.IID,
        tCartridge.IPID,
        tCartridge.IRECTYPE,
        tCartridge.CBK,
        tCartridge.INO,
        tCartridge.TDT,
        tCartridge.CPARTY,
        tCartridge.CCARTRIDGEMAKE,
        tCartridge.CCARTRIDGENO,
        tCartridge.IMAXREFILLS,
        tCartridge.IEXTREFILLS
    FROM tCartridge tCartridge
    WHERE tCartridge.tDT BETWEEN (SELECT US1.dValue
                                    FROM uSettings US1
                                        JOIN uSettings US2
                                            ON US2.iID = US1.iPID
                                    WHERE US2.CKEY = 'Current Year'
                                        AND US1.CKEY = 'From')
                            AND (SELECT US3.dValue
                                    FROM uSettings US3
                                        JOIN uSettings US4
                                            ON US4.iID = US3.iPID
                                    WHERE US4.CKEY = 'Current Year'
                                        AND US3.CKEY = 'To')
    ORDER BY tCartridge.iNo, tCartridge.cBk, tCartridge.tDt


If I have two parameters filled in, logically IF vp_cBk <> '' OR vp_iNo <> 0 THEN the following query with the replaced WHERE clause needs to run (else the one above):
SELECT tCartridge.IID,
        tCartridge.IPID,
        tCartridge.IRECTYPE,
        tCartridge.CBK,
        tCartridge.INO,
        tCartridge.TDT,
        tCartridge.CPARTY,
        tCartridge.CCARTRIDGEMAKE,
        tCartridge.CCARTRIDGENO,
        tCartridge.IMAXREFILLS,
        tCartridge.IEXTREFILLS
    FROM tCartridge tCartridge
    WHERE tCartridge.CBK = ?vp_cBk AND tCartridge.INO = ?vp_iNo
    ORDER BY tCartridge.iNo, tCartridge.cBk, tCartridge.tDt


Keeping the rest of the query the same I tried to do the following WHERE clause but it failed for me:
    WHERE IIF(?vp_cBk <> '' OR ?vp_iNo <> 0, tCartridge.CBK = ?vp_cBk AND tCartridge.INO = ?vp_iNo
        , tCartridge.tDT BETWEEN (SELECT US1.dValue
                                    FROM uSettings US1
                                        JOIN uSettings US2
                                            ON US2.iID = US1.iPID
                                    WHERE US2.CKEY = 'Current Year'
                                        AND US1.CKEY = 'From')
                            AND (SELECT US3.dValue
                                    FROM uSettings US3
                                        JOIN uSettings US4
                                            ON US4.iID = US3.iPID
                                    WHERE US4.CKEY = 'Current Year'
                                        AND US3.CKEY = 'To'))

I tried CASE WHEN THEN END instead of IIF( , , ) no joy.

Please advise on if and how I can have two different WHERE clause depending on external parameters having values.  Till now I have passed parameters but the WHERE has always been one that adapted to the values passed as parameters.

Hope I was able to explain well my issue

Thanks and regards
Bhavbhuti