SQL Wont Run, but works fine in MSSQL EM 1

[1DMF]

Hi,

I have the following command in my access database

SELECT Prod_Provider, COUNT(1) As Tot FROM dbo_Business_Register WHERE (Adv_MemNo = '" & Forms!contacts.[Membership Number] & "') AND (Ins_Lnk IS NULL) GROUP BY Prod_Provider ORDER BY Tot DESC

But it errors with

too few parametes, expected 1

If I remove the ORDER BY Tot , it works fine, why is this happening.

If I run the exact same query direct in the Enterprise Manager console , it executes fine and gives me my desired results, so why won't MS Access run the query?

Thanks,

1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

... ORDER BY [!]2[/!] DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

I don't understand your highlight with the number 2 PHV?,

Anyway I resolved the problem with

ORDER BY COUNT(1)

Seems SQL EM accepts the 'Tot' field which is dynamically generated when the query is run but the access doesn't.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

Seems like you even don't tried my suggestion ...
2 meant the second item in the SELECT list.

 

[1DMF]

So you can refer to columns via an index number?

And don't be like that PHV, I didn't try your sugestion because I'd solved it before I saw you reply.

I came back to the thread to say it was OK and I'd solved the problem and that was when I saw your post.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

you can refer to columns via an index number?
In fact, via the ordinal posotion in the SELECT list.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[1DMF]

Cool, I never knew that, that really is handy to know.

Many thanks.
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts