Query works in SQL Srvr not in Access/ASP

[MaxGrenk]

I have this query working in SQL, but it does not work when passed in via ASP into MS Access:

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
 FROM webdb.mdb.tblMenu AS m,
 webdblog.mdb.tblMenuCounter AS l
 WHERE m.menu_hidden = False
 AND m.menu_accesslevel = 0
 AND l.menu_id = m.menu_id
 AND HitsPerDay >= 1
ORDER BY HitsPerDay DESC;

ASP reports the erro as: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.


When I remove either or both lines referencing HitsPerDay it works:

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
 FROM webdb.mdb.tblMenu AS m,
 webdblog.mdb.tblMenuCounter AS l
 WHERE m.menu_hidden = False
 AND m.menu_accesslevel = 0
 AND l.menu_id = m.menu_id;

Any hint would be much appreciated. Thanks.

[Ocean10000]

try the following. I don't think access handles HitsPerDay the same way as MS SQL does.

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
WHERE m.menu_hidden = False
AND m.menu_accesslevel = 0
AND l.menu_id = m.menu_id
AND (l.menu_count / (l.menu_lastaccess-m.menu_created)) >= 1
ORDER BY (l.menu_count / (l.menu_lastaccess-m.menu_created)) DESC;

[MaxGrenk]

Thank you kindly -- this works!

I played with the query in design view, and sorted on the HitsPerDay, which generated the code for the ORDER BY, which I then copied into the AND >=1.

Again, thank you kindly for your reply!

[bmcgee]

You can also use ordinal positions in your SORT BY in Access. So you could have Sort By 1 (I think it is 0-based but you can test to confirm)

[MaxGrenk]

 SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
  FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
  WHERE m.menu_hidden = False
   AND m.menu_accesslevel = 0
   AND l.menu_id = m.menu_id
   AND (l.menu_count / (l.menu_lastaccess-m.menu_created)) >= 1
ORDER BY 1 DESC;

... works too :)
Thanks!