Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MonthName function in SELECT statement
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get monthname | Excel Discussion (Misc queries) | |||
How I can get monthname from the following formula | Excel Discussion (Misc queries) | |||
MONTHNAME when getting external data from Access | Excel Worksheet Functions | |||
MonthName to Integer from Current to Previous Year | Excel Programming | |||
Macro: MonthName Function, runtime error 424 | Excel Programming |