SQL Query Statement - ORDER BY problem

[hinsdale1]

Be aware that this stuff is way over my pay-grade, so any dumbing down of replies is appreciated



I was happy to get one of my SQL statements expressed well enough to actually generate usable results. Except i wanted them in order of last date worn (maxofdateworn).


SQL = "SELECT TOP 4 MAX(tblDatesWorn.[DateWorn]) AS MaxofDateWorn, tblOutfits.[ID], tblOutfits.[similar], tblImage.[txtImageName] FROM (tblImage INNER JOIN tblOutfits ON tblImage.ID = tblOutfits.Image) INNER JOIN tblDatesWorn ON tblOutfits.ID = tblDatesWorn.Outfit WHERE ((tblOutfits.[similar])=" & Me.[Similar] & ") GROUP BY tblOutfits.ID, tblOutfits.similar, tblImage.txtImageName ORDER BY MaxofDateWorn;"



So when I add the ORDER BY statement at the end ... the script chokes and errors:

Run Time error 3061

Too Few parameters. Expected 1

I'm stumped ? *

* not that that is terribly difficult to do.

[R_Badger]

You are very close, you need to specify ASC or DESC.

SQL = "SELECT TOP 4 MAX(tblDatesWorn.[DateWorn]) AS MaxofDateWorn, tblOutfits.[ID], tblOutfits.[similar], tblImage.[txtImageName] FROM (tblImage INNER JOIN tblOutfits ON tblImage.ID = tblOutfits.Image) INNER JOIN tblDatesWorn ON tblOutfits.ID = tblDatesWorn.Outfit WHERE ((tblOutfits.[similar])=" & Me.[Similar] & ") GROUP BY tblOutfits.ID, tblOutfits.similar, tblImage.txtImageName ORDER BY MaxofDateWorn ASC;"

Edit: ASC means Ascending DESC means Descending (Just in case)

[hinsdale1]

Sorry i did not include that info in my original post, I had tried both ASC and DESC with the same error code.

I am not sure if its because the MaxofDateWorn is not part of the aggregate function (its not in the Group By)? or because it's a MAX function in the statement? or if there is some weird BOF being introduced? or because of the nested INNER JOINS? I just cant get my pea brain wrapped around it..

Results are completely as expected without the ORDER By statement, just not in the desired order.

still stumped! *

*still not particularly hard to do

[hinsdale1]

I should mention that the debug takes me to the next line after the SQL statement:

Set RS = CurrentDb.OpenRecordset(SQL)

Almost like there are no results generated by the SQL when the ORDER BY parameter is introduced?

[R_Badger]

It's telling you there is an error in the Order By clause itself when it tries to execute it. This probably sounds crazy, but try pasting your sql into a query, if it runs correctly without error it may be a problem opening a recordset like that (I had a similar problem with a union query) if that is the case the solution for my union query was to save it as a query and then simply direct the recordset to open it.

I hope that makes sense! (Bit hot at work and my brain is boiling)

[hinsdale1]

Well your suggestion helped me locate the problem! When testing as a query I discovered it was looking for a parameter value for MaxofDateWorn when using that field to ORDER by. I substituted the ("AS") MaxofDateWorn with the original MAX(tblDatesWorn.[DateWorn]) and it worked!

So i guess you can't use an "AS" variable from the SQL statement itself to ORDER BY because it has not yet been defined.

Thanks for taking the time R. ... you are a scholar and a gentleman.. and I wish I could buy your brain a frosty mug of beer to cool it down (but you don't drink your ale cold over there in the isles?)