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

Runtime error 3085: Undefined function / Problems with DSum

patjones
Expert 100+
P: 931
Good morning all:

In what seems like an ongoing saga to make the DSum function do what I need it to, I am now having trouble with a user-defined function in my VBA module. Here's the offending line of code:

Expand|Select|Wrap|Line Numbers
  1. Me!txtLostDaysNoPaySeasonal = DSum("getNumberOfWeekdays([tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "qryTest", "[tblEmployees]![fldPayDistCode] = 'BK21312'")
In this, getNumberOfWeekdays is a function that I define elsewhere in the module, as such:

Expand|Select|Wrap|Line Numbers
  1. Function getNumberOfWeekdays(dteStartDate As Date, dteEndDate As Date) As Integer
  2. * * *
  3. (A bunch of code to obtain number of weekdays between two dates because the 'w' option in DateDiff does not work)
  4. * * *
  5. End Function
But, when I run the application, it stops at the DSum line, saying "Run time error 3085: Undefined function 'getNumberOfWeekdays' in expression." As far as I can tell, DSum is supposed to be able to accept a user-defined function in that first argument. So it seems like Access isn't recognizing my function definition in the first place.

So, I imported all my stuff into a new database, and restarted the machine just in case corruption might be an issue, but I get the same result.

Weird, no?
Sep 20 '07 #1
Share this Question
Share on Google+
3 Replies


P: 47
In a DSUM expression the syntax is
DSum("field","table","criteria expression")

I can see it should be possible to use a user-defined function to generate the criteria expression, but using it to define the table name or field name seems to be pushing it a little.
Sep 20 '07 #2

patjones
Expert 100+
P: 931
In a DSUM expression the syntax is
DSum("field","table","criteria expression")

I can see it should be possible to use a user-defined function to generate the criteria expression, but using it to define the table name or field name seems to be pushing it a little.
Well, I was previously using a DateDiff expression in the "field" argument (until I realized I needed to obtain weekdays only) - and it took it just fine. Furthermore, the Access help pages state that you can put a more complicated expression in that argument, including a call to a user defined function. So I'm not sure what's going on...
Sep 20 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Although you can use expressions in this function the logic can be quite complicated. You can use a function but as far as I know you can only involve one field.

Try something like this instead.

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2.  
  3.     Set rst = CurrentDb.OpenRecordset("qryTest")
  4.  
  5.     rst.FindFirst "[tblEmployees]![fldPayDistCode] = 'BK21312'"
  6.     Me!txtLostDaysNoPaySeasonal = getNumberOfWeekdays(rst!fldDateOfNoPayBegin, fldDateReturned)
  7.  
  8.     rst.Close
  9.     Set rst = Nothing
  10.  
Make sure that there is a reference ticked to the Microsoft DAO library.
Oct 3 '07 #4

Post your reply

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