ORDER BY causing me fits. Help Please????

F

Frederick Wilson

Hello all,

The string below does not work in its current state. However, if I
remove the final line "ORDER BY e.EVENT_DESC;" and dress up the line
before it with a ";" it seems to work fine. However, the list is not in
any order. What am I doing wrong.

For what it's worth I use the below to adjust a list box's rowsourse.
Kind of a wizard, if you will.



strSQl = "SELECT '*' as EVENT_ID, 'All Capabilities' as EVENT_DESC FROM
tblEvent " & _

"UNION " & _

"SELECT e.EVENT_ID, e.EVENT_DESC " & _

"FROM tblEvent as e " & _

"INNER JOIN " & _

"(SELECT Event_ID FROM jtblFacilityCapabilities WHERE
PK_Weapon_ID = " & strWhere & ") as j " & _

"ON " & _

"e.Event_ID = j.Event_ID " & _

"ORDER BY e.EVENT_DESC;"
 
D

David S via AccessMonster.com

Try removing the "e." from in front of "e.EVENT_DESC" - I'm pretty sure this
will fix your problem...
 
V

Van T. Dinh

I think the ORDER BY clause should use the Field Table / Field names of the
first Select component of the Union Query, not the subsequent Select
components.

Try:

strSQl = "SELECT '*' as EVENT_ID, 'All Capabilities' as EVENT_DESC " & _
" FROM tblEvent " & _
" UNION " & _
" SELECT e.EVENT_ID, e.EVENT_DESC " & _
" FROM tblEvent as e " & _
" INNER JOIN " & _
" ( SELECT Event_ID FROM jtblFacilityCapabilities WHERE " & _
" PK_Weapon_ID = " & strWhere & ") as j " & _
" ON e.Event_ID = j.Event_ID " & _
" ORDER BY tblEvent.EVENT_DESC;"
 
F

Frederick Wilson

My goodness. What is the Haccums Razor? The correct answer normally is
the simplest explanation.

I guess that is the great thing about these news groups, more eye to
find the simple stupidity of us rookies.

I sure will try this Van, Thank you so much.
 
J

John Vinson

Achems Razor, perhaps???
Click to expand...

Spelling was pretty much at the whim of the writer in the time of
William of Occam (ca. 1300 - ca. 1349); I've seen his name spelled
Ockham, Occam, and I wouldn't be surprised at either of these
alternatives!


John W. Vinson[MVP]
 
V

Van T. Dinh

I guess there weren't many learnt people to dispute the spelling in his era
....

I only know of one spelling, though ...

Thanks & cheers
Van
 
J

John Vinson

I guess there weren't many learnt people to dispute the spelling in his era
...

I only know of one spelling, though ...
Click to expand...

Well, you're Dutch... you spell things wierd anyhow! <g, d & r>

John W. Vinson[MVP]
 
V

Vincent Johns

Frederick said:
It's all good, at least you know what I was talking about.
Click to expand...

For me, the burning question has always been, did William of Occam have
a beard, and if not, did he shave himself? (Oops -- I guess that's a
subset of Russell's paradox, some centuries later. Sorry.)
 
Top