"Undefined Function ... In Expression"

[rmmahara]

Hi Folks,

I've been reading these forums for a while and now I'm in desperate need of help, so I thought I'd post!

Background:

I'm creating a Service Dashboard to track my team's adherence to Service Levels. As part of this, I've set up a web page which pulls data from a query in Access 2003. The page shows which tickets we're working on, how long they've been open, the service level for each ticket etc.

Since we're a 9-5, Monday to Friday support setup, I'm using a module that I found online to calculate how long a ticket has been open in Business Days (code below). This works perfectly from within Access. However, when I try to load up the web page from IE, I get two errors: "Data provider failed while executing a provider command" followed by "Undefined function 'WorkingDays' in expression."

I've got my function called WorkingDays in a module named "modWorkingDays ".

I've been looking everywhere for a solution, and can't seem to find one. I read something about certain functions not being visible from outside Access, but I don't know if this is the case. To test it, I tried using an example from the Visual Basic Language Developer's Handbook - it calculates Working Days in a completely different way. However, when I use this alternate code and open the web page, I get the same error.

I haven't posted the SQL query code as it's really messy (I have a very long IIF statement in one of my query columns), but it looks normal - the only thing I notice is that the function WorkingDays is in there, but there's no reference as to where the function is located. Is this normal (ie. SELECT WorkingDays(... iif statement...) AS DashPriority FROM etc. etc.

Any thoughts at all would be helpful at this point... I'm at a point where I've tried everything I can think of, and nothing is helping.

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

[jeffstl]

I am confused on the web page part of this.

Are you saying that your web page is strictly an HTML table that is exported from access?

Are you saying you are seeing these errors on the web page or inside access?

What kind of page is it that is loading? .asp? .aspx? .htm?

I will say that if you are trying to run this code by simply pasting it into an HTML document its not going to work like it does in access. Hope I'm not overstepping my assumption by saying this, but it kind of sounds like thats what your saying in your post.

[rmmahara]

Hi - thanks for replying!

Let's see if I can explain it better this time...

I've got an Access .mdb file. In the database, I've got a few linked tables - these are linked to an SQL database. I've got some queries that pull data from these tables into a usable format.

I want this data to be accessable through web pages, so I created a few Data Access Pages, using these queries as the source material. This works fine - I can load the .htm pages and see all the data in real-time. However, the data that is shown isn't correct, because I need to show business days, rather than linear days. This is where the code comes in.

Back in Access - I used the above code as a module. In my query, I call this function in order to calculate the number of Business Days between two dates. This works fine within Access, and gives me the right numbers. However, when I build a Data Access Page based on this new query (with the function call in the query), it looks fine within Access, but once I try to load the .htm page in IE, I get the error within IE.

So, to sum up:

Database: MS Access 2002
Data Source: Linked table, SQL Server
Function: Within Access, used in Access query
DAP: .htm file, calling data from Access query

Does that help?

I am confused on the web page part of this.

Are you saying that your web page is strictly an HTML table that is exported from access?

Are you saying you are seeing these errors on the web page or inside access?

What kind of page is it that is loading? .asp? .aspx? .htm?

I will say that if you are trying to run this code by simply pasting it into an HTML document its not going to work like it does in access. Hope I'm not overstepping my assumption by saying this, but it kind of sounds like thats what your saying in your post.