Create recordset ordered by autonumber (SQL)

[drazeni]

I have the following string for which I create a querydef to be used in a report. Unfortunately I can't seem to make it sort by "autonumber". Although if I paste the sql statement into an access query design view, it works. Anyone got any idea?

SELECT * FROM [order details] WHERE [order number]" _
& "= " & [prompt_order_number] & "order by " & autonumber & ";"

Thanks.

"All is not as it seems"

 

[mp9]

What is the name of your autonumbered field? Is it Autonumber? If so, try:

SELECT * FROM [order details] WHERE [order number]" _
    & "= " & [prompt_order_number] & "order by autonumber;"

http://www22.brinkster.com/accessory/

 

[drazeni]

Thanks mp9 I did already try it but upon second attempt after your post, it worked, because I previously missed a space before the "order by" in which case it joined the "prompt_order_number" value and the word "order" and gave a syntax error and I automatically assumed that the fixed field name "autonumber" was a bit fussy.

Thanks has been resolved.

"All is not as it seems"