How to sort data when using selective listboxes in Base

Hello,

For working wit selective listboxes, I use the following macro:

Sub LoadList_Customer(oEvent)
    Dim oForm As Object
    Dim oShop_country As Object
    Dim oShop_name As Object
    Dim sSelectedItem As String
    Dim sSQL As String
    Dim bClearList As Boolean
    oForm = ThisComponent.DrawPage.Forms.getByName("MainForm")
    oShop_country = oForm.getByName("lb_CC1")
    oShop_name = oForm.getByName("lb_CN1")
    sSelectedItem = oShop_country.SelectedValue
    bClearList = 0
    If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
	sSQL = "Select ""Shop_name"" || ' - ' || ""Shop_place"" || ' - ' || ""Shop_adress"", ""Shop_id"" FROM ""Table_Customer"" WHERE ""Shop_country"" = '" & sSelectedItem & "' ORDERBY ""Shop_name"" || ' - ' || ""Shop_place"" || ' - ' || ""Shop_adress"""
	oShop_name.ListSource = array(sSQL)
	oShop_name.refresh()
    If bClearList Then
    	While oShop_name.ItemCount > 0
        		oShop_name.removeItem(0)
    	wend
    End If
End Sub 

This works fine, but my problem is:

In the following line of the coding for the macro, i would like to add a sort of data in cell 2 before selecting as this gets overwriiten when selection needs to be altered. I tried adding ORDERBY at several spots, but I run into errors.

If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1 sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’”

I tried things like:

sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’ ORDERBY ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress”””

analog to sql in initial query in the second cell:

SELECT “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress”, “Shop_id” FROM “Table_Customer” WHERE “Shop_country” = ‘Austria’ ORDER BY ( “Shop_name” || ’ - ’ || “Shop_place” || ’ - ’ || “Shop_adress” ) ASC

What am I doing wrong?

@grexup,

No need to spread question over question and two comments. You can Edit your question to add more.

Also use Preformatted text icon on toolbar (Upper left corner with question/answer) to properly format code.

I try to find this Icon, but I do not see it. Would like to be able to format text more clear.

This also goes that I can’t find how to attach jpg or other files.

Hello,

Do not exactly understand what:

sort of data in cell 2

or

analog to sql in initial query in the second cell

actually mean. Please use actual field names. “Cell” is not a valid reference.

Sorting is quite simple. Specify the field(s) you want to sort on:

sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & "’ ORDERBY ““Shop_place””

will sort by Shop_place. You can also add asc (ascending) or desc (descending).

OR

sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & "’ ORDERBY ““Shop_adress””, ““Shop_name”” asc

will sort by Shop_adress then by Shop_name in ascending sequence.

Edit 2020-08-11:

@grexup stated:

I try to find this Icon, but I do not see it. Would like to be able to format text more clear.
This also goes that I can’t find how to attach jpg or other files.

First edit your question. In lower right of your question is the edit selection:

image description

Once you have selected that you can add/modify your question. The toolbar for items such as formatting text and adding files can be found in the upper left portion of the question:

image description

Edit 2020-08-11 Second edit:

Found obvious problem I have overlooked. Made the mistake of copying the SQL from the original post and using that in the answer. The problem is that ORDERBY needs to be two words → ORDER BY and not one.

When entering the ORDERBY command as you stated (I added double quotes at the end for closing the line), I loose functionality of the 2nd listbox where customer from the right country is to be selected (In the first cell you choose the country, which triggers the second listbox to show customers from only that country)
The second listbox either shows the list of customers that are in the sql statement when going there via Control → Data → List content. or shows nothing at all.

This is the macro I use:

Sub LoadList_Customer(oEvent)
Dim oForm As Object
Dim oShop_country As Object
Dim oShop_name As Object
Dim sSelectedItem As String
Dim sSQL As String
Dim bClearList As Boolean
oForm = ThisComponent.DrawPage.Forms.getByName(“MainForm”)
oShop_country = oForm.getByName(“lb_CC1”)
oShop_name = oForm.getByName(“lb_CN1”)
sSelectedItem = oShop_country.SelectedValue
bClearList = 0
If IsEmpty(sSelectedItem) Then sSelectedItem = 0: bClearList = 1
sSQL = “Select ““Shop_name”” || ’ - ’ || ““Shop_place”” || ’ - ’ || ““Shop_adress””, ““Shop_id”” FROM ““Table_Customer”” WHERE ““Shop_country”” = '” & sSelectedItem & “’ ORDERBY ““Shop_adress””, ““Shop_name”” asc”
oShop_name.ListSource = array(sSQL)
oShop_name.refresh()
If bClearList Then
While oShop_name.ItemCount > 0
oShop_name.removeItem(0)
wend
End If
End Sub

As i mentioned without this ORDERBY clause in the macro it is working fine. What am I missing?

After I ran the macro and want to look at changes in SQL command, I get this error message:
“Syntax error in SQL statement → syntax error, unexpected NAME, expecting $end”

I can open it then and view the SQL. It added then the ORDERBY part:

Select “Promo_country” || ’ - ’ || “Promo_description”, “Promo_id” FROM “Table_Promo_Items” WHERE “Promo_type” = ‘Items’ ORDERBY “Promo_country”, “Promo_description” asc.

What do i miss here?

Post a sample Base file with the problem.

See → How do I attach a file to my question/answer?