SQL/ACCESS QUERY

[markdt]

Hi All,

I am trying to reproduce a qeury that i use from within access, the code for the query is

SELECT MonthName(Month(tblConcern.ConDate)) AS MDate, Count(tblConcern.ConcernID) AS CountOfConcernID
FROM tblConcern
GROUP BY MonthName(Month(tblConcern.ConDate));

This runs fine within access but soon as i put it into my ASP page and try and execute it i receive the following error message:

Microsoft JET Database Engine error '80040e14'

Undefined function 'MonthName' in expression.

/concern/despchart.asp, line 34

Any ideas anyone?

Thanks
Mark

 

[monksnake]

Can you post your code exactly as you have it, including your connection string and record set creation?

<.

 

[travisbrown]

Don't know why that won't run, but if you just return the MONTH integers in the recordset, you can use the VBS function MONTHNAME on the return values. At least until you figure out why JET or ADO is being ornery.

You could save the query as a view in Access and query that instead of passing the whole query in through ADO.

 

[markdt]

Hi All,

Thanks for your replys i have since fixed my problem using the monthname function within the asp page instead of trying to execute the monthname function from within the query. Using the sql string below in asp converted the dates in 01/01/2001 format to month numbers. I then used monthname(ors("MDate")) to convert the month numbr to a name.

strQuery = "SELECT month(tblConcern.ConDate) AS MDate, Count(tblConcern.ConcernID) AS CountOfConcernID"
strQuery = strQuery & " FROM tblConcern"
strQuery = strQuery & " GROUP BY month(tblConcern.ConDate), Year([ConDate])"
strQuery = strQuery & " HAVING (((Year([ConDate]))=2007));"

strXML = strXML & "<set label='" & monthname(ors("MDate")) & "' value='" & ors("CountOfConcernID") & "' />"

Thanks
Mark