MonthName function in SELECT statement

[Sunbank]

Hi there

I am using MS Office/XP at the Excel and Access 2002 levels.

I am trying to use the MonthName function in a SELECT statement from
Excel to an access database. I have tried to narrow the root cause
down by building a simple test harness and associated Access table.
The table ('Runs') has 1 row of 2 columns: 'RunDate' defined as a date
(short format) field and 'RunMonthNumber' defined as an integer field:

Sub Test()
Dim i As Integer
Dim Conn As ADODB.Connection
Dim DBPath As String
Dim ADOError As ADODB.Error
Dim R_Set As ADODB.Recordset
Dim SQLString As String

On Error GoTo ErrorHandler

DBPath = "C:\Documents and Settings\User.Name\My Documents"
SQLString = "SELECT Runs.RunDate, Runs.RunMonthNumber, {fn
MonthName(Runs.RunMonthNumber)} AS MyMonth FROM Runs;"

Set Conn = New ADODB.Connection

With Conn
.CursorLocation = adUseClient
.Mode = adModeRead
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open DBPath & Application.PathSeparator & "TestDataBase.mdb"
End With

If Not Conn Is Nothing Then
If Conn.State = adStateOpen Then
Set R_Set = New ADODB.Recordset
R_Set.Open SQLString, Conn, adOpenStatic, adLockOptimistic
If Conn.Errors.Count = 0 Then
MsgBox "Date = " & R_Set("RunDate").Value & ", month
number = " & R_Set("RunMonthNumber").Value
Else
GoTo ErrorHandler
End If
If R_Set.State = adStateOpen Then R_Set.Close
Set R_Set = Nothing
Conn.Close
Set Conn = Nothing
End If
End If

Exit Sub

ErrorHandler:
If Not R_Set Is Nothing Then
If R_Set.State = adStateOpen Then R_Set.Close
Set R_Set = Nothing
End If
If Not Conn Is Nothing Then
If Conn.Errors.Count > 0 Then
For Each ADOError In Conn.Errors
Debug.Print ADOError.Description
Next
End If
If Conn.State = adStateOpen Then Conn.Close
Set Conn = Nothing
End If
End Sub


The first problem to occur, with a SELECT statement such as:

SELECT Runs.RunDate, Runs.RunMonthNumber,
MonthName(Runs.RunMonthNumber) AS MyMonth FROM Runs;

is that I receive the message: "Undefined function 'MonthName' in
expression."

So, having researched this a little more, I realised that it's
necessary to insert an escape clause, e.g.

SELECT Runs.RunDate, Runs.RunMonthNumber, {fn
MonthName(Runs.RunMonthNumber)} AS MyMonth FROM Runs;

This then gives the error: "Malformed GUID. in query expression '{fn
MonthName(Runs.RunMonthNumber)}'"

I get the same results whether I try to use MonthName on the integer
database field or the date field.

I think I understand what's happening: I guess the Jet OLEDB provider
does not support even the "escaped" MonthName function, or the
MonthName function is expressed in the wrong format for the Jet driver
to understand. For example, the research that I've done indicates
that, for some database drivers, the date expression must be a date
rather than the integer that the VBA MonthName function expects.

I also tried to simulate this through both the Query user interface of
MS Access - of course, it works without problem. When trying it
through the MS Query interface, it also works and, by recording the
macro through Excel, I see that the driver Excel is using is different:

With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\User.Name\My
Documents\TestDatabase.mdb;DefaultDir=C:\Documents and Sett" _
), Array( _
"ings\neil.miller\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
))
.CommandText = Array( _
"SELECT {fn MonthName(Cint(Right(Runs.RunYearAndMonth,2)),
False)} FROM Runs;" _
)
.Refresh BackgroundQuery:=False
End With

I seem to have been keeping within "best" practice with the connection
string shown in my test harness and, do not, unless for good reason,
want to change this.

Does anyone have any ideas whether I can get the MonthName to work
somehow using the Jet OLEDB provider?

Many thanks, in advance, for your help.

Neil