Order By - No value given for one or more required parameters.

Can you not use Order By on a nested query?

[chris_f]

Hi All,

I have a nested sql query. Each nested query is a simple sql query that returns one field and is followed by the 'as' command. For example

. . . (sub query) as FieldName . . .

One nested query returns and date and another returns a time. I am trying to order the entire query by the date and time descending so that the newest appears at the top.

If I run the query without an 'order by' commands it appears fine (just in the wrong order), however, if I add

. . . ORDER BY DateFieldName desc, TimeFieldName desc

at the end I get the following error message

Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.

Does anyone have any suggestions?
Chris

[Xoc]

I think you misspelled a field name.

[chris_f]

Thanks Xoc,

I can't have. Copy and Paste job.

Checking again ...

Chris.

[chris_f]

The field names are correctly spelt. Their called later in the page and this would have to be mispelt as well.

Chris

[WebJoe]

I think you have to use

ORDER BY (sub query)

[chris_f]

WebJoe,

Thanks for the idea, however, this would not do what I'm looking for. The subqueries only return one row which means ordering will do nothing.

Chris.

[aspdaddy]

Yep, if you are using fieldname AS [Alias] SQL will moan when you use [Alias] in the ORDER BY.

Try using the actual fieldname in the ORDER BY. Just tried this and it works ok:

SELECT ID AS [Booking Reference]
FROM (SELECT ID FROM Bookings)
ORDER BY ID

[chris_f]

Thanks ASPDaddy,

I've tried what you said but it is still isn't working :(

I'm just going to have to replicate the fields in the other table for the time being.

Thanks for you help.
Chris

[chris_f]

I've replicated the fields instead of using a nested query.

Thanks for you help.
Chris.