Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    England
    Posts
    15

    Question Unanswered: Parameterized queries - works in Access but not SQLS2k?

    I have an application where users can enter data into any (or all) of 6 search fields,
    to produce a filtered query.

    This works fine using my Access version(see code below),
    but as SQLS2k cannot use "IIF", I tried to replace these bits with
    "CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
    as these cannot be "wild-carded" in the same way as Access allows.

    Can anyone suggest a way forward that does not involve coding all the
    possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?

    Hoping you can help
    Alex








    PARAMETERS
    CurrentType Text,
    CurrentCategoryID Long,
    CurrentProductID Long,
    CurrentClientID Long,
    CurrentContractID Long,
    FromDate DateTime,
    ToDate DateTime;

    SELECT
    tAudit.AuditID,
    tAudit.ActionType,
    tAudit.ClientID,
    tClients.ContactCompanyName,
    tAudit.ContractID,
    tContracts.ClientRef,
    tAudit.ProductID,
    tProducts.ProductName,
    tAudit.CategoryID,
    tCategories.CategoryName,
    tAudit.Acknowledged,
    tAudit.ValueAmount,
    tAudit.DateStamp

    FROM (((tAudit
    LEFT JOIN tCategories
    ON tAudit.CategoryID = tCategories.CategoryID)
    LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
    LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
    LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID

    WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
    AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
    AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
    AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
    AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
    AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    (tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
    Mdaaa....
    tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
    ?????

  3. #3
    Join Date
    Jan 2003
    Location
    England
    Posts
    15
    Thanks Buser - works fine - much obliged to you!

    Regards
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •