By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,329 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

"Undefined Function ... In Expression"

P: 2
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.



Expand|Select|Wrap|Line Numbers
  1. Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
  2. '....................................................................
  3. ' Name: WorkingDays
  4. ' Inputs: StartDate As Date
  5. ' EndDate As Date
  6. ' Returns: Integer
  7. ' Author: Arvin Meyer
  8. ' Date: May 5,2002
  9. ' Comment: Accepts two dates and returns the number of weekdays between them
  10. ' Note that this function has been modified to account for holidays. It requires a table
  11. ' named tblHolidays with a field named HolidayDate.
  12. '....................................................................
  13. On Error GoTo Err_WorkingDays
  14.  
  15. Dim intCount As Integer
  16. Dim rst As DAO.Recordset
  17. Dim DB As DAO.Database
  18.  
  19. Set DB = CurrentDb
  20. Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
  21.  
  22. StartDate = StartDate + 1
  23. 'To count StartDate as the 1st day comment out the line above
  24.  
  25. intCount = 0
  26.  
  27. Do While StartDate <= EndDate
  28.  
  29. rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
  30. If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  31. If rst.NoMatch Then intCount = intCount + 1
  32. End If
  33.  
  34. StartDate = StartDate + 1
  35.  
  36. Loop
  37.  
  38. WorkingDays = intCount
  39.  
  40. Exit_WorkingDays:
  41. Exit Function
  42.  
  43. Err_WorkingDays:
  44. Select Case Err
  45.  
  46. Case Else
  47. MsgBox Err.Description
  48. Resume Exit_WorkingDays
  49. End Select
  50.  
  51. End Function
Feb 28 '08 #1
Share this Question
Share on Google+
2 Replies


jeffstl
Expert 100+
P: 432
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.
Feb 28 '08 #2

P: 2
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.
Mar 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.