SQL way to specify multi-column index name as the ORDER BY?

[vbaInet]

I know that you know but we have to be careful. Others see that kind of thing and wonder why it doesn't work for them.

It is also good opportunity to provide explanation for the casual reader to understand why we must set the object first. I know I used the code more than once while scratching my head as to why something so similar to so many other code samples didn't work for CurrentDb in a single line.

True... it's always good to clarify things because these threads are read by people with differing levels of capability. I will bear that in mind next time

 

[mdlueck]

I didn't say that no loops were necessary, simply that you use them unnecsarily.

But your example code still uses the loop, and you say that I use loops "unnecsarily". If it was not necessary, then do not use a loop in your counter suggestion. Else leave my loops alone.

On the other thread I mentioned you used a loop to requery a form in exactly the same way as you looped through the querydefs collection. You replied saying that the same code had worked for you in many other places.

I believe that (Where a loop would work but a direct call would raise an error) was on the thread where I was coming to the realization that I have a corrupted form situation and am going back two weeks in time to find a backup copy without said form in it. NOT FUN!

In the same thread you repeatedly called an Event Procedure an API. Bob Larson explained the difference to you in detail and you defended your terminology saying they were the same thing to you. And you are still calling your procedure an API on this thread.

API / Event / Procedure / Subroutine / Method / EIEIO = "All named portions of code which are callable". API just happens to be the shortest name of the bunch. I have since changed my terminology soas to not ruffle feathers.

fin

 

[vbaInet]

API / Event / Procedure / Subroutine / Method / EIEIO = "All named portions of code which are callable". API just happens to be the shortest name of the bunch. I have since changed my terminology soas to not ruffle feathers.

fin

Arguing this will cause your thread to become rather long. They are not the same and don't mean the same. I would advise you look up the differences.

 

[mdlueck]

Really? What about:

Set db = CurrentDb
db.QueryDefs("myquery").SQL = whatever

I updated the code to the following:

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Update the query the form is using
  Set daoQDF = daoDB.QueryDefs(strQueryDef)
  With daoQDF
    .SQL = strSQL
    .Close
  End With

There even is a way to "safe delete" a DAO,QueryDef object directly by known name, and code it to safely fail it the object does not exist in the first place. Slick!

 

[MSAccessRookie]

You are not understanding...

I do have ORDER BY in the code.

The ORDER BY logic expects a single word to be what is should ORDER BY. That name is the column name, which I have the indexes (single column) match the column name. Also part of the control specific to a column has the same name.

The wrench comes with wanting to specify MULTIPLE columns in the ORDER BY clause.

Would be nice if Access would allow ORDER BY indexname and said index would sort by multiple columns.

Better?

I have been following this conversation, and do not see where anyone has addressed the point that you overlooked regarding the way ORDER BY works.

You said:

"The ORDER BY logic expects a single word to be what is should ORDER BY."

This is ALMOST True, but it misses another option.

Galaxiom Suggested:

"ORDER BY [some name], [another name], [yet another]

This should do what you want (with three Fields)

An Alternative might be to combine the two thoughts like this:

ORDER BY [some name] & [another name] & [yet another] OR
ORDER BY [some name] + [another name] + [yet another]

This works in both SQL Query Format and SQL VBA Format.

I am sure I must be missing a bigger picture here, but if this helps any, feel free to try it out.

 

[mdlueck]

You said:

"The ORDER BY logic expects a single word to be what is should ORDER BY."

This is ALMOST True, but it misses another option.

(chuckle...) There always is one more exception case to code for. Fortunately all sorts in this application are single column, with the exception of the Revision / Version curve ball.

Thanks!