Ask Your Question
0

How to sort data when using selective listboxes in Base

asked 2020-07-25 14:17:48 +0200

grexup gravatar image

updated 2020-07-26 04:20:27 +0200

Ratslinger gravatar image

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?

edit retag flag offensive close merge delete

Comments

@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.

Ratslinger gravatar imageRatslinger ( 2020-07-26 04:23:29 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-07-26 04:32:47 +0200

Ratslinger gravatar image

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 flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-07-25 14:17:48 +0200

Seen: 39 times

Last updated: Jul 26