current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I wrote a SQL query in MS Access that uses the MonthName function. In access it works flawlessly. I copied the exact SQL statement into an excel module that I frequently use to query databases. When I run the query, excel keeps telling me that MonthName is an undefined function name. If I remove the MonthName portion, the query runs fine.

It seems like I'm missing a reference or something... Right now, I'm referencing Microsoft ActiveX Data Objects Library 6.0. Can anyone point me in the right direction? Thanks

strSQL = "SELECT DISTINCT Customers.CustomerName, Employees.EmployeeName, [Policy data revised].EXDT, MonthName(Month([EXDT])) AS expMonth 
          FROM (([Service Team table] 
          INNER JOIN Customers 
          ON [Service Team table].CustID = Customers.CustID) 
          INNER JOIN Employees 
          ON [Service Team table].EmployeeID = Employees.EmployeeID) 
          INNER JOIN [Policy data revised] 
          ON Customers.CustID = [Policy data revised].CustID 
          WHERE ((([Service Team table].RoleExtension)='2.  Underwriting Assistant') 
          AND (([Policy data revised].EXDT) 
             BETWEEN #" & minExpDt & "# AND #" & maxExpDt & "#)) 
          ORDER BY [Policy data revised].EXDT ASC;"
share|improve this question
 
Just for future reference, what version of Excel are you using? –  Gord Thompson May 23 '13 at 17:50
add comment

2 Answers

up vote 3 down vote accepted

The MonthName() function is only available for queries run within an Access application session. See the "The following VBA functions won't work when called from a property sheet or used in an SQL statement" bullet point at About Microsoft Jet Expression Service sandbox mode. Within an Access application session, the db engine can use the expression service to use that sandboxed function.

Since you can't use MonthName, try this Format expression instead.

Format([EXDT], 'mmmm') AS expMonth
share|improve this answer
 
Worked like a charm. Thanks. –  Ben Gorman May 23 '13 at 17:41
add comment
up vote 1 down vote

Strange, I just tried the following code in Excel 2010 (64-bit) and it worked for me:

Sub foo()
'' Reference: "Microsoft ActiveX Data Objects 6.0 Library"
Dim con As ADODB.Connection, rst As ADODB.Recordset
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Gord\Desktop\Database1.accdb;"
Set rst = New ADODB.Recordset
rst.Open "SELECT MonthName(Month([BookingStart])) FROM Payment_tbl", con, adOpenStatic, adLockOptimistic
Debug.Print rst(0).Value
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
End Sub

Edit

Further to HansUp's comment, something must have changed between Office 2007 and Office 2010. I ran another test from a 32-bit machine running Office 2010 and the above code run against an Access 2000 .mdb file worked for me using both...

Provider=Microsoft.ACE.OLEDB.12.0;

...and...

Provider=Microsoft.Jet.OLEDB.4.0;

I also checked the SandBoxMode registry value under...

HKLM\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines

...and it is 3, which is the value for "Enabled" (ref: here).

share|improve this answer
 
Interesting. I took OP's word that it failed. When I test from Office 2007, I get "-2147217900 Undefined function 'MonthName' in expression." I wonder if the sandbox properties are different with 2010. Format(date_field, 'mmm') works, though. Or 'mmmm' for full instead of abbreviated month name. –  HansUp May 23 '13 at 17:32
 
@HansUp I think you're right that something must have changed with Office 2010. I have updated my answer to elaborate. –  Gord Thompson May 23 '13 at 18:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.