[Solved] ListBox not working after adding 2 columns to query

[karinincanada]

I've based a ListBox on a query (qryAuthors) that had two columns: AuthorID and a concatenated column called Fullname. It was working fine.

The ListBox SQL: SELECT "Fullname", "AuthorID" FROM "qryAuthors"

I wanted to sort the records by last name/first name, so I added two more fields in the query to accomplish this, which seems to have broken the ListBox:

Query in which I am sorting the data by last name, and then by first name.

author sort.PNG (9.72 KiB) Viewed 2532 times

Query as it is now, with 4 columns

query output.PNG (6.21 KiB) Viewed 2532 times

Since I added these two fields the list box doesn't display the list anymore or display the data in the field. I tried unchecking the view checkboxes but this didn't seem to do anything.

Any ideas?

[QuazzieEvil]

uncheck the 'visible' check box for those 2 last columns

[karinincanada]

Yeah, I tried that before. I tried it again just now, and it didn't change anything -- the list box is still not working.

[Villeroy]

Sometimes I have a small list box to display one line while the drop down property is not set. This occurs after I converted some other type of control to a list box.
Drag the multi-line box taller or set the drop down property.

[karinincanada]

Dropdown property is set to yes, and I made it taller, and that made no difference.

However, if I delete the 2 "sorting" columns from the query, save the query, and reopen the form, the form works fine.

I'm uploading the database - it's very small and simple at this point. Maybe someone could take a peek under the hood.

Right now there are 2 queries - the one named qryAuthors does not have the 2 extra columns and you'll see that the list box on the form works.

If you rename the qryAuthors query to something else, and rename the other query (which has the 2 "sort" columns) to qryAuthors, you'll see how it makes the list box stop functioning.

PS -- I thought I updated my signature, but just fyi, I have version 3.2.

[Arineckaig]

Since I added these two fields the list box doesn't display the list anymore or display the data in the field.

I am not entirely sure if I understand the problem correctly, but have attached a revised copy of your Base file. You will see that the only only change is to add an ORDER BY clause to the SQL command that fills the list box on the form. My apologies if this answer is off beam.

[karinincanada]

Aha!! I get it!

I've been going about this all wrong! I didn't even NEED a query, I can do it all in the SQL (Structured QUERY Language, duh). Obviously I'm a noob in the SQL department. I went into the SQL statement and modified what you did so that it sorts by the Last name, and THEN by the First name, and now it works PERFECTLY, no query required.

Thank you very much! My whole procedure is altered now. I'll be doing my Access databases this way from now on - less objects to deal with, hurray.

Here is the SQL for any followers of the post:

SELECT "AuthorFirstName" || ' ' || "AuthorLastName" AS "Fullname", "AuthorID" FROM "Author" AS "Author" ORDER BY "AuthorLastName" ASC,"AuthorFirstName" ASC

And here's what it looks like in the SQL build screen (sorry I don't know the ooBase term for this):