Hi,

I am using a form to allow a user to select what fields they want to display in a query. I have managed to get this working very basically, using a list box and SQL statement in VB.

What I'd like to do is take it one step further and allow the user to specify the order of the fields in the query (and the sort order), is there anyway of achieving this ? I thought there may be a field selector control I could use, similar to the one in the first stage of the Simple Query Wizard ?

Any assistance would be appreciated.

Cheers,

Mark.

You can create an OrderBy and build it as a string.

strOrderBy=strOrderby & Forms!FrmMyForm!ControlName

You need to make sure that this string "strOrderBy" is exaccly what would go into the OrderBy Part of the sql and then you will be all set.

This should be fairly similiar to what you have done to create the select statement based on user selections in the listbox.

Good Luck!

Thanks for the reply.

I can see what you're saying, but I don't see how I can get this to work. Using a listbox the user can only select the fields in the order they appear in the listbox, it's not possible to re-order them. I suppose I could use a few drop down boxes to get a sort order, but this wouldn't allow the user to re-order the fields as they appear in the query results. Perhaps I'm just over doing it !

Cheers,

Mark.

Perhaps you could have a click event on the list box that builds the fields as a string into a text box in the order they were clicked.

Or use 2 list boxes and have the second recieve items from the first as they are clicked or moved over by button click in the order they are moved. I understand what you are trying to do and I think it is really neat!

Another option would be to build several OrderBy statements in code and selec the proper one based on what the user has selected on the form. You could even use an option group with check boxes and have descriptions of the ordering next to each check box.

Best of luck!

I think I've found a solution using 2 listboxes, one containing the field list and one blank list box. I have then set the OnDblClick property on the field list textbox to add the relevant field name to the blank list box. Then I set the OnDblClick property on the blank list box to remove the field.

This functions in a similar fashion to the Simple Query Wizard field chooser. I can then use the blank list box control to build the SQL statements.

Cheers for all the help.

Mark.

I was in the middle of typing my previous reply, when you posted your's!

Sounds like a good idea to me

Cheers again for the help,

Mark.