SQL Woes

[hdog]

I have written an SQL statement that works fine in MS Access (as a query) but when I move it into my ASP page as a new Dreamweaver recordset definition it does not work. Dreamweaver is giving me an error stating that "Missing ),] or item in query expression'Last(Month([Enrollment_Information]![enrollment_date]')." I can't see anything missing from the sql. Any help would be appreciated. Thanks in advance.

Here is my sql:

SELECT Member_Information.UserName, Member_Information.UserID, Max(Enrollment_Information.enrollment_date) AS MaxOfenrollment_date, Last(Month([Enrollment_Information]![enrollment_date]) & "/" & Day([Enrollment_Information]![enrollment_date]) & "/" & (Year([Enrollment_Information]![enrollment_date])+[Enrollment_Information]![enrollment_period])) AS Renewal_Date
FROM Member_Information INNER JOIN Enrollment_Information ON Member_Information.UserID = Enrollment_Information.UserID GROUP BY Member_Information.UserName, Member_Information.UserID HAVING (((Member_Information.UserID) Like UID))
ORDER BY Max(Enrollment_Information.enrollment_date)

 

[jemminger]

instead of Access syntax
Last(Month([Enrollment_Information]![enrollment_date])

try chaning to ANSI SQL syntax
Last(Month(Enrollment_Information.enrollment_date)

change all similar occurrences

=========================================================
-jeff

http://www.jeffemminger.com

try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[hdog]

That was one of the first things I tried to no avail. Any other suggestions?

 

[DreXor]

as far as i know SQL doesn't suppot first and last functions of access.

secondly on int or numeric fields, like is sometimes puked upon, when doing your joins, try making UID and ID fields set equal not like to each other.

lastly, to try and make first and last functionality, you'll have to do a select top 1 * from table order by <field of importance> asc/desc

asc/desc is based on if you want first or last from it.

DreX
aKa - Robert