Undefined function ‘getDates’ in expression

[frozbie]

Hi

I want to display data in Excel that is pulled from an Access database. The aim is to display a cross tab query that shows a weeks worth of data regardless of whether there is data for every day of the week (always display five day columns even if Tuesday has no data...)

My solution was to create a table which stores days of the week, a user defined public function in an Access module which accepts a date parameter for the first day of the week and depending on the week day number passed to it, returns the date of each day.

This works great when running the cross tab query in access and I can see a summarised total for each week day. However, when I call the cross tab query from Excel VBA I get the error: "Undefined function ‘getDates’ in expression"

Details of the Access setup below.

Searches on different forums have found several people with the same error but no solution. There are a couple people saying it simply is not possible to call an Access function from within a query when calling that query from outside Access.

But I live in hope...! Is there a way of running an Access query containing a function from Excel VBA?

Thanks

Mark

Query: qryDay

PARAMETERS [setDate:] DateTime;
SELECT tbl_Day.PK_Day, tbl_Day.Day_Name, getWeekDates([setDate:],[PK_Day]) AS DayDate
FROM tbl_Day
WHERE (((tbl_Day.PK_Day)<6));

Public Function getWeekDates(dtSetDate As Date, lngDayOfWeek As Long) As String
    '// function to return a weeks worth of dates based on parameters supplied
    '// to enable query to return employee hours based on shift pattern
    
    '//assumption is that date will always be a Monday!
    
    getDates = DateAdd("d", (lngDayOfWeek - 1), dtSetDate)
    
End Function

tbl_Day contains:
PK_Day Day_Name
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday

Dim con As ADODB.Connection 
    Dim rst As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim strDB As String
    
    strDB = "Data Source=" & strDB_Path 'defined elsewhere
    
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDB_Path & ";"

    Set cmd.ActiveConnection = con
    
    cmd.CommandText = "qryDay"
        
    Set rst = cmd.Execute(, Array(CDate("21/01/2013")), adCmdStoredProc)
    'fails with error at this line

 

[pr2-eugin]

frozbie, did you copy the function getDates into a module of Excel VBA?

 

[frozbie]

Hi pr2-eugin,
No, not initially. I've just tried but I still get the same error message. Why did you suggest that?
Regards
Mark

 

[pr2-eugin]

Mark, make sure that the Module Name is not the same as the Function Name.. getDates() is a User defined function and hence needs to be defined where it is being used.. since you are suing in Excel it needs to be copied into the Excel workbook you are using..

 

[Brianwarnock]

I've never done any of this but if the function has to be in Excel I would think that it has to be as an Addin.

Brian

 

[ChrisO]

How is getDates() being called?

Chris.

 

[frozbie]

Mark, make sure that the Module Name is not the same as the Function Name.. getDates() is a User defined function and hence needs to be defined where it is being used.. since you are suing in Excel it needs to be copied into the Excel workbook you are using..

Hi Paul,

The module name was not the same as the function name
I'm unsure why you think creating the function in Excel would work for a function being called as part of an Access query.
I did try that as you suggested but I get the same error: "Undefined function ... in expression"

Regards

Mark

 

[frozbie]

How is getDates() being called?

Chris.

Hi Chris,

My original post has the query SQL and you should be able to see the function call as a column in the query.

I have a workaround:

Turns out I don't really need the dates as column headers, nice as it would have been to provide that.

The query as shown - but without the function to provide date - works fine in terms of giving me five rows, one for each week day. I can then link this with an outer join to other queries to give me values and place the resulting recordset in a cross tab to give me a weeks view of data.

If anyone can ever advise a way to call an Access VBA function from within an Access query that is being executed from Excel VBA code... please let me know!

Regards

Mark

 

[ChrisO]

The original seems to call getWeekDates not getDates.

Chris.

 

[frozbie]

The original seems to call getWeekDates not getDates.

Chris.

Thanks Chris, So much for my attempts to modify code to make publically available... The function was named correctly in the original query but I changed some details when posting and failed to notice I had not changed every instance of the name. Now corrected.