SQL order by problem

[iroosma]

I am currently using this line of code to oder a table based on the values of rec.

DoCmd.RunSQL "SELECT mid([rec],3,6) FROM driver ORDER BY rec "

When I run this it gives me an error saying that what is inside the quotes is not an SQL statement. Can someone help me out and see what the problem is.

Thanks

 

[BobJacksonNCI]

I'm a novice (with Access experience), but this may work:

DoCmd.RunSQL "SELECT substr(rec,3,6) FROM driver ORDER BY rec "

SQL uses substr instead of mid



HTH,
Bob

 

[iroosma]

I know that mid([rec],3,6) works in SQL because something similar works in many other areas of my program.

Thanks for you help though

 

[BobJacksonNCI]

Sorry,

Forgot which forum I was in - was thinking of SQL Server, not SQL code within Access...

It may be that "Order By Rec" requires that Rec be a selected field or the result name of an expression. E.G.:

DoCmd.RunSQL "SELECT mid([rec],3,6) as Rec FROM driver ORDER BY rec "


HTH,
Bob

 

[OhioSteve]

This is the proper syntax:

SELECT Mid([fieldname],3,6) AS alias
FROM tablename
ORDER BY tablename.fieldname;

You forgot to give the field an alias.

You realize that you are displaying on a fragment of the field, but you are sorting on the entire field.

 

[iroosma]

I tried

DoCmd.RunSQL "SELECT mid([rec],3,6) AS alias FROM driver ORDER BY rec"

DoCmd.RunSQL "SELECT mid([rec],3,6) AS alias FROM driver ORDER BY driver.rec"

neither one of these worked either. If anyone still has any ideas, please help.

Thanks

 

[lespaul]

What they said to try was this:

DoCmd.RunSQL "SELECT mid([rec],3,6) AS alias FROM driver ORDER BY alias"


Leslie

 

[iroosma]

I tried

DoCmd.RunSQL "SELECT mid([rec],3,6) AS alias FROM driver ORDER BY alias"

also and it still gives me the same error.

A RunSQL action requires an argumnet consisting of an SQL statement.

Is there maybe some sort of library I need to use order by. This is the only part of my program left that doesn't work. If anyone still has any ideas, let me know.

Thanks

 

[lespaul]

what happens if you build this query in the design grid or run the SQL directly, can you get it to work that way?


Leslie

 

[BNPMike]

RunSQL needs an action query. Select doesn't work.

 

[BNPMike]

If RunSQL worked (which it won't) you would generate a set of records. What did you intend to do with that set? If you ran the SQL in QBE it would display a datasheet. Is that what you want, or do you want a recordset you can maniopulate with VBA?

(RunSQL will work fine if - say - you wanted to delete records, or create a table - but not if you want to return a recordset since that requires not only the database to do something but also some handler to sensibly deal with the records returned)

 

[OhioSteve]

I have three comments.

First, SQL statements in Access terminate with a semicolon. Your example lacks a semicolon.

Second, the Access SQL syntax should not be a mystery. I am just making these SQL statements using the query design window. Then I put the SQL syntax onto the clipboard. The Access SQL engine produces ugly code, but the code does run.

Third, the Access VBA syntax for running a select query is~

doCmd.OpenQuery ("queryName")

Remember that Access differentiates select queries and action queries.