use list box to filter cross tab query

[siamandm]

Hi i have this sql query below

Code: Select all

 Dim SQL As String
    Dim itm As Variant
    Dim strWhere As String
    
    SQL = "PARAMETERS [Forms]![frmFilterBen]![lstItems] Short " & _
" TRANSFORM Nz(Sum([qryBen-Items].ItemQty),0) AS SumOfItemQty " & _
" SELECT [qryBen-Items].YaoCode, [qryBen-Items].FirstName, [qryBen-Items].FamilySize, " & _
"[qryBen-Items].PhoneNo1 , [qryBen-Items].PDSNo, " & _
" [qryBen-Items].CitizenIDNo, [qryBen-Items].Status, [qryBen-Items].LocationName," & _
" Count([qryBen-Items].DateReceived) AS CountOfDateReceived " & _
" FROM [qryBen-Items] " & _
" WHERE((([qryBen-Items].ItemName_ID) in  [Forms]![frmFilterBen]![lstItems].itemdata())" & _ ////// this is myt where clause which need tobe fixed
" GROUP BY [qryBen-Items].YaoCode, [qryBen-Items].FirstName, [qryBen-Items].FamilySize," & _
" [qryBen-Items].PhoneNo1, [qryBen-Items].PDSNo, [qryBen-Items].CitizenIDNo, [qryBen-Items].Status, [qryBen-Items].LocationName " & _
" PIVOT [qryBen-Items].ItemName;"

my list box is called lstitems , and when i press this button i want to run the query qryBen-item

regards

[HansV]

Can you explain what role the list box should play? Is it a multi-select list box?

[siamandm]

i want to use the list box to make multi selection of items to be filtered
regards

[HansV]

The SQL would look like this:

Code: Select all

    Dim SQL As String
    Dim itm As Variant
    Dim strWhere As String

    SQL = " TRANSFORM Nz(Sum(ItemQty),0) AS SumOfItemQty " & _
        "SELECT YaoCode, FirstName, FamilySize, " & _
        "PhoneNo1, PDSNo, " & _
        "CitizenIDNo, Status, LocationName, " & _
        "Count(DateReceived) AS CountOfDateReceived " & _
        "FROM [qryBen-Items] "

    For Each itm In Me.lstItems.ItemsSelected
        strWhere = strWhere & "," & Me.lstItems.ItemData(itm)
    Next itm

    If strWhere <> "" Then
        SQL = SQL & "WHERE ItemName_ID In (" & Mid(strWhere, 2) & ") "
    End If

    SQL = SQL & _
        "GROUP BY YaoCode, FirstName, FamilySize, " & _
        "PhoneNo1, PDSNo, CitizenIDNo, Status, LocationName " & _
        "PIVOT ItemName"

You have to decide what to do with it next. You cannot just "open" a SQL statement. You can open a recordset on this SQL statement, and do something with that.

[siamandm]

if i want to show the result in a subfrm called result , i just need to Me.result.Form.RecordSource = SQL ?

regards

[HansV]

Yes, you can do that.

[siamandm]

thanks a lot, I will give it a go and see how it's going ...
regards

[siamandm]

Yes, you can do that.

seems to be I have a problem with creating form from the cross tab query, and I think this is because the column items are not static!
any suggestions, please?

regards

[HansV]

Yes, that is a problem. One option would be to fix the item names. At the end, use

Code: Select all

    SQL = SQL & _
        "GROUP BY YaoCode, FirstName, FamilySize, " & _
        "PhoneNo1, PDSNo, CitizenIDNo, Status, LocationName " & _
        "PIVOT ItemName In ('Item1','Item2','Item3','Item4')"

Make sure that you use the same names on the form.

[siamandm]

thanks Hans

1- i have to provide all the items name?
2- is there any other better and professional way to do this filter?


Regards

[HansV]

1. Yes, you have to provide all item names. If you want, you can get them from the items table. Let's say this table is named tblItems:

Code: Select all

    Dim SQL As String
    Dim itm As Variant
    Dim strWhere As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strIn As String

    SQL = " TRANSFORM Nz(Sum(ItemQty),0) AS SumOfItemQty " & _
        "SELECT YaoCode, FirstName, FamilySize, " & _
        "PhoneNo1, PDSNo, " & _
        "CitizenIDNo, Status, LocationName, " & _
        "Count(DateReceived) AS CountOfDateReceived " & _
        "FROM [qryBen-Items] "

    For Each itm In Me.lstItems.ItemsSelected
        strWhere = strWhere & "," & Me.lstItems.ItemData(itm)
    Next itm

    If strWhere <> "" Then
        SQL = SQL & "WHERE ItemName_ID In (" & Mid(strWhere, 2) & ") "
    End If

    SQL = SQL & _
        "GROUP BY YaoCode, FirstName, FamilySize, " & _
        "PhoneNo1, PDSNo, CitizenIDNo, Status, LocationName " & _
        "PIVOT ItemName"

    Set rst = dbs.OpenRecordset("tblItems", dbOpenForwardOnly)
    Do While Not rst.EOF
        strIn = strIn & ",'" & rst!ItemName & "'"
        rst.MoveNext
    Loop
    rst.Close

    SQL = SQL & " In (" & Mid(strIn, 2) & ")"
    Me.result.Form.RecordSource = SQL

2. Not if you need to use a crosstab query. Because the columns in a crosstab query aren't static, they are difficult to use as record source for forms and reports.