Runtime error 3085: Undefined function / Problems with DSum

[patjones]

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:

[CODE=vb]Me!txtLostDaysN oPaySeasonal = DSum("getNumber OfWeekdays([tblWC]![fldDateOfNoPayB egin], [tblWC]![fldDateReturned])", "qryTest", "[tblEmployees]![fldPayDistCode] = 'BK21312'")[/CODE]

In this, getNumberOfWeek days is a function that I define elsewhere in the module, as such:

[CODE=vb]Function getNumberOfWeek days(dteStartDa te As Date, dteEndDate As Date) As Integer
* * *
(A bunch of code to obtain number of weekdays between two dates because the 'w' option in DateDiff does not work)
* * *
End Function[/CODE]

But, when I run the application, it stops at the DSum line, saying "Run time error 3085: Undefined function 'getNumberOfWee kdays' 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?

[barry07]

In a DSUM expression the syntax is
DSum("field","t able","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.

[patjones]

In a DSUM expression the syntax is
DSum("field","t able","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...

[MMcCarthy]

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.

[CODE=vb]
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenR ecordset("qryTe st")

rst.FindFirst "[tblEmployees]![fldPayDistCode] = 'BK21312'"
Me!txtLostDaysN oPaySeasonal = getNumberOfWeek days(rst!fldDat eOfNoPayBegin, fldDateReturned )

rst.Close
Set rst = Nothing
[/CODE]

Make sure that there is a reference ticked to the Microsoft DAO library.