I have an Access Form that contains a ListBox that gets data from the following query:
SELECT tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR AS [Retro Frei],
tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status],
tblISIN_Country_Table.Country
FROM
(tblFUNDS INNER JOIN tblISIN_Country_Table ON tblFUNDS.ISIN = tblISIN_Country_Table.ISIN)
INNER JOIN tblMorningstar_Data ON
(tblFUNDS.Fund_Selection = tblMorningstar_Data.Fund_Selection)
AND (tblFUNDS.ISIN = tblMorningstar_Data.ISIN)
GROUP BY tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR,
tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status],
tblISIN_Country_Table.Country, tblFUNDS.Fund_Selection
HAVING (((tblFUNDS.RDR) Like Nz([Forms]![frmMain]![ddnRDR],'*')) AND
((tblMorningstar_Data.[DEU Tax Transparence]) Like Nz([Forms]![frmMain]![ddnTax],'*')) AND
((tblMorningstar_Data.[Distribution Status]) Like Nz([Forms]![frmMain]![ddnDistribution],'*'))
AND ((tblISIN_Country_Table.Country) Like Nz([Forms]![frmMain]![ddnCountry].[Text],'*'))
AND ((tblFUNDS.Fund_Selection)=0));
I have set up the various controls referenced by the query to run the same SQL statement above on the _AfterUpdate event of clicking the various dropdown fields. They all execute, which I can tell by a) the list box updating and b) by setting break points.
The issues is this:
When I change the value of the dropdown field for country for example, it filters by country. If I then set the dropdown field for Tax, it fileters for Tax, but ignores the value set in the country dropdown control (and all values in the other dropdowns as well).
My question:
Why does this happen and how can I get it to filter based on the values of ALL the dropdown fields at once?
Like in comment, try:
SELECT tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR AS [Retro Frei], tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status], tblISIN_Country_Table.Country
FROM (tblFUNDS INNER JOIN tblISIN_Country_Table ON tblFUNDS.ISIN = tblISIN_Country_Table.ISIN) INNER JOIN tblMorningstar_Data ON (tblFUNDS.Fund_Selection = tblMorningstar_Data.Fund_Selection) AND (tblFUNDS.ISIN = tblMorningstar_Data.ISIN)
WHERE (((tblFUNDS.RDR) Like Nz([Forms]![frmMain]![ddnRDR],'*')) AND ((tblMorningstar_Data.[DEU Tax Transparence]) Like Nz([Forms]![frmMain]![ddnTax],'*')) AND ((tblMorningstar_Data.[Distribution Status]) Like Nz([Forms]![frmMain]![ddnDistribution],'*')) AND ((tblISIN_Country_Table.Country) Like Nz([Forms]![frmMain]![ddnCountry].[Text],'*')) AND ((tblFUNDS.Fund_Selection)=0))
GROUP BY tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR, tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status], tblISIN_Country_Table.Country, tblFUNDS.Fund_Selection;
Sorry to state the obvious but are the values of the other textboxes still there after you have updates the country combobox? Try passing them to a message box or storing them in variables that you can watch to see exactly what parameter is being passed to the query
Glad to hear you've solved your problem. To elaborate my comment I will place the code to change the controlsource of the listbox here anyway. Maybe someone else will find it useful one day. Any comments are also welcomed.
Public Function Rapport_query()
Dim sqlTax As String
Dim sqlRDR As String
Dim sql As String
Dim selectQuery As String
Dim whereStatement As String
Dim i As Integer
Dim i1 As Integer
Dim i2 As Integer
'set counter (because if the filter is not the first there should be an "AND" operator before the filter.
i = 0
'check if the combobox is empty, if it's not use the input as input for you where statement
If Not (IsNull(Forms!frmMain!ddnRDR)) Then
i1 = i + 1
sqlRDR = " tblFUNDS.RDR LIKE " & Chr(34) & Forms!frmMain!ddnRDR & Chr(34)
i = i + 1
End If
If Not (IsNull(Forms!frmMain!ddnTax)) Then
i2 = i + 1
If i2 > i1 And i > 0 Then
sqlTax = " AND tblMorningstar_Data.[DEU Tax Transparence] LIKE " & Chr(34) & Forms!frmMain!ddnTax & Chr(34)
Else
sqlTax = "tblMorningstar_Data.[DEU Tax Transparence] LIKE " & Chr(34) & Forms!frmMain!ddnTax & Chr(34)
End If
i = i + 1
End If
'if the lenght is 0, there are no filters. Else fill the where statement string
If Len(sqlRDR & sqlTax) = 0 Then
whereStatement = ""
Else
whereStatement = "WHERE " & sqlRDR & sqlTax
End If
'set the select query
selectQuery = "SELECT tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR AS [Retro Frei]," & _
"tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status]," & _
"tblISIN_Country_Table.Country " & _
"FROM (tblFUNDS INNER JOIN tblISIN_Country_Table ON tblFUNDS.ISIN = tblISIN_Country_Table.ISIN) " & _
"INNER JOIN tblMorningstar_Data ON (tblFUNDS.Fund_Selection = tblMorningstar_Data.Fund_Selection) " & _
"AND (tblFUNDS.ISIN = tblMorningstar_Data.ISIN) " & _
"GROUP BY tblFUNDS.MorningsStar_Fund_Name, tblFUNDS.ISIN, tblFUNDS.RDR," & _
"tblMorningstar_Data.[DEU Tax Transparence], tblMorningstar_Data.[Distribution Status]," & _
"tblISIN_Country_Table.Country , tblFUNDS.Fund_Selection"
'combine the select query with the variable where statement
sql = selectQuery & whereStatement
'set the listbox controlsource
Forms!frmMain.ListBox.ControlSource = sql
End Function
For completeness's sake my answer so it is easier to find for someone else in the future:
The issue was that the ddnCountry form had a bound column that referenced the wrong column. It didn't match the column that I was using the ListBox query!
