Dictinct values in a list box (1 Viewer)

Finance · Sep 14, 2018

Code:

Private Sub ListCountrySE_AfterUpdate()
    Dim varItem As Variant
    Dim strSQL As String
        
       If Me.ListCountrySE.ItemsSelected.Count Then
        strSQL = "Select ID_Number, Nature_of_Fees from TotalCostsSmallEntity where [Country] in ('"
        For Each varItem In Me.ListCountrySE.ItemsSelected
            strSQL = strSQL & Me.ListCountrySE.ItemData(varItem) & "','"
        Next varItem
        strSQL = Left(strSQL, Len(strSQL) - 2) & ") ORDER BY ID_Number"
    Else
        strSQL = "Select ID_Number, Nature_of_Fees from TotalCostsSmallEntity ORDER BY ID_Number"
    End If
    
    Me.ListNOFSE.RowSource = strSQL
End Sub

I need the Nature_of_Fees list box to only pull in distinct values from the TotalCostsSmallEntity database.
Can someone help me edit the above code so that strSQL result only shows distinct values ?

thanks!!

isladogs · Sep 14, 2018

If ID_Number is a PK field you should only get unique values anyway.

Otherwise try SELECT DISTINCT ID_Number, .....

Finance · Sep 14, 2018

I have tried SELECT DISTINCT ID_Number but its only selecting distinct ID numbers and not nature of fees.

isladogs · Sep 14, 2018

If you have the same 'fees' values on different ID_Number values you will get separate records.
If you only want the fees field, omit the ID_Number field

Finance · Sep 14, 2018

The Nature of Fees Listbox is not selecting anything if I remove the ID number.
I think this is happening because the nature of fees listbox is programmed to get populate according to the "ListCountrySE" list box i.e the list of countries.

cCountries and Nature of Fees are linked through the ID Number field.

i.e for each ID_Number there is a country and a fee description.

is there a way to make the nature of fees distinct, while retaining the ID_Number?

isladogs · Sep 14, 2018

Suggest you post one or more screenshots of the form/listbox and some data to show you exactly you have.

Also back in post 1, what is the If line meant to do?
Should it be

Code:

if ......Count >0 Then

MarkK · Sep 14, 2018

I don't mind the use of the .Count property there as a boolean. If the list is empty the count is zero and the If expression is false. Done. No need for " > 0"
IMO
Mark

Finance · Sep 14, 2018

I have attached a copy of the database.

The form I am having a problem in is Selection Form Small Entity.

the If line says that if a country is selected in the Country listbox, the nature of fees, in the nature of fees box, is sorted according to the selection in the country listbox.

isladogs · Sep 14, 2018

I've looked at the form, selected a country and observed the filtering in action.
But I'm still baffled. As far as I can see, each item in the nature of fees listbox is unique.
Perhaps I'm missing something but I don't see the problem.

Can you explain what you want to see for a specific country e.g. Canada

moke123 · Sep 14, 2018

Your using an "IN" clause so i dont think it makes any difference.

... country in ("'USA','Germany','USA','France'")
will pull the same records as
... country in("'USA','Germany','France'")

You probably shouldn't be storing data in your table names.

Why not have a table of countries and use a foreign key rather than text.

Finance · Sep 16, 2018

@Ridders I have kept the entry Filing Fees the same for three countries to test the code. The rest is distinct yes because i needed to differentiate the costs between countries for the end user of the database. I need to make it efficient.

Finance · Sep 16, 2018

moke123 said:
Your using an "IN" clause so i dont think it makes any difference.

... country in ("'USA','Germany','USA','France'")
will pull the same records as
... country in("'USA','Germany','France'")

You probably shouldn't be storing data in your table names.

Why not have a table of countries and use a foreign key rather than text.

Would you mind elaborating on your suggestion?
Do you mean creating a new table with only countries and a country ID which will be the foreign key?
and then correlating the nature of fees in the cost sheet with the country ID?

moke123 · Sep 17, 2018

From what I recall you had about 5 identical tables, which each having a different Country name appended to the table name. That's storing data AS the table. It would make sense to have one table and have a foreign key to a lookup table for the CountryID's.

Finance · Sep 18, 2018

The other tables are calculation tables feeding data into the main cost sheet table.
For example: examination costs Brazil is calculating the examination costs using a formula for a given number of claims and the calculated result is being updated in the cost sheet based on the Fees_ID

Dictinct values in a list box (1 Viewer)

Finance

Registered User.

isladogs

MVP / VIP

Finance

Registered User.

isladogs

MVP / VIP

Finance

Registered User.

isladogs

MVP / VIP

MarkK

bit cruncher

Finance

Registered User.

Attachments

isladogs

MVP / VIP

moke123

AWF VIP

Finance

Registered User.

Finance

Registered User.

moke123

AWF VIP

Finance

Registered User.

Similar threads

Users who are viewing this thread