Hi all, firstly i'm very new at coding so please forgive me if i sound unexperienced, i am
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 get confused with this sort of thing and don't know what to do, can someone help?
Also at the moment, with any areas or towns picked and the filter applied, it returns no records
Any idea's? I have the report grouped by town and then area, would this affect it?
Here's the code:-
Edit: 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 think the grouping doesn't affect it at all as i have tried without any grouping, but it's something to do with the relationship between area and town? Yes/no?

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

Also at the moment, with any areas or towns picked and the filter applied, it returns no records

Here's the code:-
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
Edit: 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


Last edited: