Order By is DAO open

[GregoryWest]

I have a little code here. If I include the code with the ORDER BY clause I crash with a 3095 error. If I comment the ORDER BY out, and execute the second select command instead everything works.

Question, what am I missing. I have tried braces, bracket, parenthesizes you name it nothing seems to work

code:
strSql_in = "SELECT * FROM MailRecs ORDER BY [MailRecs.owneraddr1] where active;"
'strSql_in = "SELECT * FROM MailRecs where active;"
MsgBox strSql_in
Set rs_in = DBEngine(0)(0).OpenRecordset(strSql_in)

 

[theDBguy]

Hi. Have you tried it this way?

strSql_in = "SELECT * FROM MailRecs where active ORDER BY MailRecs.owneraddr1;"

 

[NauticalGent]

Without digesting the SQL statement, have you tried designing the query in the query builder to see if it runs without error.

If it does, you could switch to SQL view and see how Access writes it. Just a thought...

 

[CJ_London]

think this

ORDER BY [MailRecs.owneraddr1]

should just be

ORDER BY MailRecs.owneraddr1

or even just

ORDER BY owneraddr1

 

[GregoryWest]

Hi guys, thanks for the quick replies. Yes tried the various ways of putting MailRecs.owneraddr1, also tried making a query in the builder and copying the SQL over. This crashed too, same error message.



Very confusing

 

[CJ_London]

so what description are you getting for error 3095?

according to FMS it is

'Cannot have aggregate function in expression'

And why are you using DBEngine(0)(0)?

and when you use the query builder - does it work successfully? if so, what sql is it generating?​

 

[GregoryWest]

so what description are you getting for error 3095?

according to FMS it is

'Cannot have aggregate function in expression'

And why are you using DBEngine(0)(0)?

and when you use the query builder - does it work successfully? if so, what sql is it generating?​

Not sure what you are meaning by aggregate function. everything in the selected are simple fields in the table. As for why DAO once the table is opened the VBA does a huge amount of processing on the table. I could open a query (which I am doing for now but that just litters the project with a huge bunch of query's where I should just be using an Order By in the select.

 

[CJ_London]

Not sure what you are meaning by aggregate function

that's my point - you said you are getting error 3095 but have not provided the description you are getting - so I spent a few minutes trying to find out what it is.

I don't understand the rest of your comments.

If you can't provide the answers to my questions, then I can't suggest what the problem is.

 

[The_Doc_Man]

OK, if you have tried using the "SELECT ... FROM ... WHERE .... ORDER BY ...." keyword order and that fails, then leaving out the ORDER BY works, the problem is not solely in that query unless you have a corrupted installation. My first question is to ask: What is the nature of MailRecs? Is it a table? Is it a query? If a query, show us the query.

 

[GregoryWest]

Hey DOC MAN you are the one! It works, god knows why, but it works!!!!


Here is what I found, Code A) works just fine, Code B) crashes. The only deference between A and B is ORDER BY followed by WHERE being swapped.:
A) strSql_in = "SELECT * FROM MailRecs where active ORDER BY [MailRecs.owneraddr1] ;"
B) strSql_in = "SELECT * FROM MailRecs ORDER BY [MailRecs.owneraddr1 where active] ;"

Did not think SQL was supposed to freak out over the order of the conditions. Sigh.


Greg

OK, if you have tried using the "SELECT ... FROM ... WHERE .... ORDER BY ...." keyword order and that fails, then leaving out the ORDER BY works, the problem is not solely in that query unless you have a corrupted installation. My first question is to ask: What is the nature of MailRecs? Is it a table? Is it a query? If a query, show us the query.

 

[theDBguy]

Hey DOC MAN you are the one! It works, god knows why, but it works!!!!

Here is wghat I found, Code A) works just fine, Code B) crashes. The only deference between A and B is ORDER BY followed by WHERE being swapped.:
A) strSql_in = "SELECT * FROM MailRecs where active ORDER BY [MailRecs.owneraddr1] ;"
B) strSql_in = "SELECT * FROM MailRecs strSql_in = "SELECT * FROM MailRecs ORDER BY [MailRecs.owneraddr1 where active] ;"

Did not think SQL was supposed to freak out over the order of the conditions. Sigh.

Greg

Hi Greg. Glad to hear you got it sorted out, but I can't help but think you probably didn't try my suggestion earlier. Still, glad to hear you're good to go now. Good luck with your project.

 

[The_Doc_Man]

Did not think SQL was supposed to freak out over the order of the conditions. Sigh.

Some implementations of SQL are more tolerant than others. The language specification offers a minimum syntax standard but doesn't stop vendor extensions of SQL. So if you have learned on a version with extended syntax and got away with odd ordering, you were in a non-standard environment at the time. The ordering of SQL elements is like it is because of the order in which certain things have to be done by the SQL parser. Each vendor has their own idea of how to get there starting from a text string.

The only deference between A and B is ORDER BY followed by WHERE being swapped.

Nope. If that was an accurate cut-paste then B also contains this construct: [MailRecs.owneraddr1 where active]

That construct is a syntax problem because everything inside the brackets is the name of a field - and dollars to doughnuts says you don't have a field of that name.

Now, if that was merely a typo or funky cut-paste for this reply, then there is still THIS construct: "SELECT * FROM MailRecs strSql_in = "SELECT * FROM MailRecs ORDER BY [MailRecs.owneraddr1 where active] ;"


Again, if this is merely a cut-paste done carelessly while building your response, then whoopsie! If the cut-paste was accurate, I'm not sure what SQL thinks you gave it - but I know I wouldn't like it.

I'm not trying to bust your chops, though. I just want you to realize that we can only go on what you post for us to see. If your method of construction of a post leaves us scratching our heads, then maybe we won't give the best possible advice. You help yourself by being careful in how you build a post.

 

[GregoryWest]

Sorry that was a typo I did not see. It has been fixed. My Bad