none
Unable to Populate Values after selecting multiple parameter value

    Question

  • I'm not able to populate state as shown in image. Though transaction dates are shown once I select stores but state are not shown after store and transaction date parameter is selected.
     


    Here is the query Im using for three of them
     
    Store:
     
    Select distinct Convert(varchar(20),store_no) as Store
    From SalesAudit.TaxExemptTrans
     
     
    Transaction Date:
     
    SELECT DISTINCT Convert(VARCHAR(20), transaction_date, 101) AS TransactionDate
      FROM SalesAudit.TaxExemptTrans
      Where convert(varchar(20),Store_no) IN (@Store)
     
     
    State:
     
    SELECT  distinct State
       FROM SalesAudit.TaxExemptTrans
       Where Convert(varchar(20),store_no) In (@Store ) 
      AND convert(varchar(20),convert(date,transaction_date)) In (@TransactionDate)
     

    Main Query:

    SELECT [tax_exempt_no]

          ,[transaction_no]
          ,[transaction_date]
          ,[register_no]
      FROM [SalesAudit].[TaxExemptTrans]
      Where 
    (Convert(varchar(20),Store_no) In (@Store) or @Store='Select All') 
    AND
    (convert(varchar(20),convert(date,transaction_date))  In (@TransactionDate) Or @TransactionDate='Select All')
    AND
    (Convert(varchar(20),state) IN (@State) Or @State='Select All')
     
     
    Let me know if you can help me.
     
    Thanks in advance.

    ZK


    • Edited by SQL_Admirer Monday, January 21, 2013 5:03 AM edited title
    Monday, January 21, 2013 5:01 AM

Answers

  • Hi ZK,

    The issue may occur due to the mismatch of the formats of the TransactionDate fields in different queries. In the second query from which the TransactionDate parameter retrieves values, its format is mm/dd/yyyy (with century 101). However, in the WHERE clause of the third query, the Convert(varchar(20),Convert(Date,transaction_date)) uses the default mon dd yyyy hh:miAM (or PM) format. To resolve the issue, please modify the WHERE clause in the third query as follows:

    Where Convert(varchar(20),store_no) In (@Store ) AND Convert(varchar(20),transaction_date,101) In (@TransactionDate)

    Reference:
    CAST and CONVERT (Transact-SQL)

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by SQL_Admirer Thursday, January 24, 2013 5:00 PM
    Tuesday, January 22, 2013 9:31 AM
    Moderator

All replies

  • Hi ZK,

    The issue may occur due to the mismatch of the formats of the TransactionDate fields in different queries. In the second query from which the TransactionDate parameter retrieves values, its format is mm/dd/yyyy (with century 101). However, in the WHERE clause of the third query, the Convert(varchar(20),Convert(Date,transaction_date)) uses the default mon dd yyyy hh:miAM (or PM) format. To resolve the issue, please modify the WHERE clause in the third query as follows:

    Where Convert(varchar(20),store_no) In (@Store ) AND Convert(varchar(20),transaction_date,101) In (@TransactionDate)

    Reference:
    CAST and CONVERT (Transact-SQL)

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by SQL_Admirer Thursday, January 24, 2013 5:00 PM
    Tuesday, January 22, 2013 9:31 AM
    Moderator
  • Thanks Mike! It worked. :)

    ZK

    Thursday, January 24, 2013 5:00 PM