Query with user input and sort by multiple fields

[ultrarunner2017]

Hi;
I have been pulling my hair out trying to figure out why I cannot create a simple query in Access 2010.
What I need to do is ask the user for input to one field. I do that by using Like "*" & [Enter the data] & "*"
Then I need to sort the results. That works fine so long as I sort only by one field (column in the design view).
But when I try to sort by more than one field, the user is asked multiple times to enter the search criteria (number of times asked is the number of fields which have a sort criteria).
If I do not ask the user for input, the query runs fine no matter how many fields it is sorted on.

this also happens if I run a query which is not sorted, but do a sort while viewing the query - then save the query when closing it. Even though the new sort criteria does not show up in the design view, it acts the same way as it would if I had entered it there.



I have read several articles on doing query sorts by multiple columns, and they talk about selecting the sort criteria in the "criteria pane". Since I cannot find anything on the Design Tools ribbon called "criteria", I am assuming that criteria is the large pane that is always opened in the main window of the design view.
If not, I really need to go back and study the multiple e-books I have for Access 2010.

In the end, I probably should not be using Queries like this. I should be creating forms, reports, etc. But since this database is really for my use only, I haven't bothered with that.

Thanks for your help
FW

[orange]

You haven't told readers anything about your database, nor requirements. I recommend you do a little research on database concepts and design.
Here is a link with lots of info on database planning and design.

Good luck.

[ultrarunner2017]

This pretty much describes the problem I am having.
https://stackoverflow.com/questions/...meter-and-sort
Yes, I agree that I need to do some research and reading.

[Micron]

I've never heard of this, nor experienced it, and I can't replicate it now. If I sort on a field with multiple variations of "shear***" and prompt for the criteria value, it runs fine with our without a sort. Did you try a compact/repair on your db? If that's of no help, I'd copy the sql from your query sql view and paste it into a new one and save it, then run it. See if the current query is corrupt.

[isladogs]

@ultrarunner
I'd never heard of the issue either and have just tested repeatedly.
After endless attempts which worked perfectly I finally managed to get it to do what you said - parameter request appeared twice.
Ran it a second time & it did it again.
Decided to save it so I could post the situation that triggered this behaviour.
I then ran it again - it behaved perfectly

Since then I've tried repeated variations on this using different fields, sorts, tables & can't get it to go wrong again.
So as Micron has already suggested, I think you may have corruption

[June7]

For your sole use or not, really could save yourself a lot of aggravation and frustration by not using popup input prompt. Just cannot validate input. Not that much effort to build a 'search' form. Instead of the generic prompt in the query, just reference control on the form, possibly a combobox LimitToList Yes so only valid data can be selected.