Problem with DSum / Undefined function in expression error...

[zepphead80]

Good morning:

I am attempting to use the DSum aggregate function in a VB module that is part of my Access database. The line of code looks like this:

Me!txtLostDaysNoPaySeasonal = DSum("getNumberOfWeekdays([tblWC]![fldDateOfNoPayBegin], [tblWC]![fldDateReturned])", "qryTest", "[tblEmployees]![fldPayDistCode] = 'BK21312'")

In this, getNumberOfWeekdays is a function that I defined elsewhere in the module as such:

Function getNumberOfWeekdays(dteStartDate As Date, dteEndDate As Date) As Integer

* * *

(Code to determine number of weekdays between two dates)

* * *

End Function

However, when I run the application, it stops at the DSum function and raises an error saying Undefined function getNumberOfWeekdays in expression. The Access help pages say that this argument in DSum can take an expression that contains a user defined function, so I'm not sure what's going on.

Any insight would be GREATLY appreciated. Thank you...

 

[Remou]

You cannot include a function in Dsum in such a way. Have you considered creating a query?

 

[jrbarnett]

Remove the double quote marks from around the call to getNumberOfWeekdays.

John

 

[zepphead80]

Thank you for replying...

When I remove the double quotes, I then get an error saying Microsoft Access can't find the field | referred to in your expression.

Also, the MS help pages say, and I quote, "If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function."

So by that reasoning I ought to be able to put my function into the DSum argument, right?

 

[Remou]

You are right about the function. I tried with a mock set up, and it worked for me. Does the code compile?

 

[zepphead80]

Hi Remou:

The code compiles fine. It fails at runtime.

When I use DateDiff() instead of my function, it works fine at runtime. But I'm not using DateDiff() because the 'w' option is broken, and I can't have weekend days in my results. So I'm stuck with the need to make my function work. Why would a built-in function like DateDiff() be OK and not a user-defined function?

 

[Remou]

The w option is broken"? In what way?

Have you tried slimming down your function to just a return to test if the problem is there?

Function getNumberOfWeekdays(dteStartDate As Date, dteEndDate As Date) As Integer

getNumberOfWeekdays=1

End Function

 

[zepphead80]

Sorry, I left that part out...

I commented out the DSum() line and ran a test on my function as such:

a=getNumberOfWeekdays(#08/24/07#,#08/29/07#)
b=MsgBox("The number of weekdays is " & a, vbOkCancel)

The message box popped up with the correct result, so I know the function works OK.

Also, I'm running Access 2007 on Windows XP...

 

[Remou]

What is the SQL of qryTest?

 

[zepphead80]

The SQL for qryTest is:

SELECT tblEmployees.*, tblWC.*, tblEmployees.fldPayDistCode
FROM tblEmployees INNER JOIN tblWC ON tblEmployees.fldERN = tblWC.fldERN
WHERE (((Right([tblEmployees]![fldPayDistCode],1))="2"));

This query does what I want it to, both when I run the application, and run it just as a stand alone query in the query design window to produce a table of results.

Basically, the query just picks out employees with a payroll distibution code ending in "2"...

 

[PHV]

Have you tried to add the getNumberOfWeekdays call in qryTest ?
Is your function declared as Public in a standard code module ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zepphead80]

I made qryTest in Access' query design window, and it resides in that part of Access...not in my VB module. How do I reference my function in the query?

Also, the function is NOT declared as Public - I just have Function getNumberOfWeekdays(* * *) for the declaration; could that affect this?

Finally, what do you mean by a standard module?

Thank you for working with me on this...

 

[jrbarnett]

A standard module is one that is:
* Not behind a form or report (ie shows up in the modules section of the database in its own right)
and
* Was not specifically created as a class module

The public declaration would depend on the type of module that was used - this is why PHV asked you.

John

 

[zepphead80]

Ah ha! This module is behind my form. I'm pretty new to VBA so how can I turn this into a standard module and keep my form connected to it?

 

[jrbarnett]

1. Go to the modules section of VBA
2. Click New

This will create a new standard module in your database.

3. Open the module that contains your code
4. Select it, then use Edit -> Copy to put it on the clipboard.
5. Move back to the new module
6. Paste it in below the line that says "Option Explicit"
7. Save the module. Give it a sensible name, don't leave it with the default of Module1. The name of the module has no bearing on how you run the subs and functions within it.
8. Delete the code from the original module
9. Save the form and its module
10. Try again.

This is because by default, subs and functions in modules behind forms and reports are accessible only to other subs and functions within that module.
However, subs and functions in standalone modules are available anywhere within your application.

For more on what I mean, search for information on variable and function accessibility scope.

John

 

[zepphead80]

Well I have done those things, but how do I tie the form to the new module? Now nothing on my form works (i.e. when I click a command button it doesn't do anything, etc.)

 

[jrbarnett]

Section 4 should read "Select the GetNumberOfWeekdays function" rather than the contents of the whole module.

The rest should go back behind the module in the form.

Apologies for any misunderstanding.

John

 

[zepphead80]

Works beautifully. Thanks so much. I have learned a crucial thing about VB modules today...