Desperately need help with Dynamic Report

[Garindan]

Hi all, I have a report which lists customer details along with details of their last purchase. It is the first dynamic report i have done. It lists customer title, surname, house number, street, area, town/city, tel, then purchase date, season, products and cost.

I have a form which i'm using as a filter which has two multi-select list boxes, one listing areas from tblAreas and the other listing town/cities from tblTown_Cities. Then an apply filter button and a remove filter button. I followed the tutorial from this page - http://www.fontstuff.com/access/acctut19.htm#dialog3

Here's a pic -

Except i tried to change it as i want it to display chosen Areas as well as chosen Towns, even if they are unrelated. But i don't know what statement to use? And at this stage none seem to work anyway . If i use AND it will need to have both Area AND Town for the record to show wont it? Which will not allow me to show different unrelated areas and towns. If i have OR it wont work either will it? Or is it because it is set to show all records if none are selected? I think i should have it to ignore the other list if nothing is selected but i don't know how.

Also at the moment, with any areas or towns picked and the filter applied, it returns no records Any idea's?

Here's the code:-

Private Sub Form_Load()
     DoCmd.OpenReport "rptCustomerDetails&LastPurchase", acViewPreview
End Sub

Private Sub cmdApplyFilter_Click()
Dim varItem As Variant
    Dim strArea As String
    Dim strTown As String
    Dim strFilter As String
' Build criteria string from lstArea listbox
    For Each varItem In Me.lstArea.ItemsSelected
        strArea = strArea & ",'" & Me.lstArea.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strArea) = 0 Then
        strArea = "Like '*'"
    Else
        strArea = Right(strArea, Len(strArea) - 1)
        strArea = "IN(" & strArea & ")"
    End If
' Build criteria string from lstTown listbox
    For Each varItem In Me.lstTown.ItemsSelected
        strTown = strTown & ",'" & Me.lstTown.ItemData(varItem) _
        & "'"
    Next varItem
    If Len(strTown) = 0 Then
        strTown = "Like '*'"
    Else
        strTown = Right(strTown, Len(strTown) - 1)
        strTown = "IN(" & strTown & ")"
    End If
' Build filter string
    strFilter = "[Area] " & strArea & _
                " AND [Town_City] " & strTown
' Apply the filter and switch it on
    With Reports![rptCustomerDetails&LastPurchase]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
     Reports![rptCustomerDetails&LastPurchase].FilterOn = False
End Sub

With the OR statement the filter returns all results, as it must be for example, Area OR everything. So it's the AND statement which is causing it to return no results, even when choosing an area which belongs to a town. Is this because the area's and towns are unrelated? But when choosing an area, and a town with the OR statement no records are returned. I'm confused I have it grouped by Town, then Area and i thought that might be the problem but then i tried without any grouping with the same results so it can't be that? , but is it something to do with the relationship between area and town?

No there must be something wrong with my code. I've tried it with just one multi-select list on tblTown_Cities, with a simple report on JUST tblTown_Cities, and if i select any towns at all it shows no records Pllleeeeaassseeee can someone help??

Or is it something to do with the fields used in the query/report?

I have the Areas multi-select list sourced on tblAreas, which is

AreaID - PK, Autonumber
Area - Text

and the Town list on tblTown_Cities, as follows

Town_CityID - PK, Autonumber
Town_City - Text
CountyID - Number, but not used in the report or query.

I remember when i was trying stuff out it came up with an error at one point, unrelated and just cos i was messing with the code, but it showed the string as using numbers, so it is obviously using the AreaID and Town_CityID fields but thats normal right? And how it should be? I'm getting quite confused

I want to be able to choose any area's or towns and have it bring up the records that contain either so i guess i need to change code to do that but i don't know how. Or perhaps someone has some quite different code i can use? I don't know, i just tried to follow a tutorial as stated earlier

Many thanks in advance to all the great people in this forum

 

[llkhoutx]

You might get meaningful reponses to your postings with simpler questions. I personally wouldn't take a hour to understand a posting.