darn function criteria

[Guest]

Hi,

I'm stuck and need some help desperatly.
what I'm trying to do is to create a universal function (stored in a module).

Inside the funtion i use the built-in function dsum to calculate something.
The dsum function needs a field, table and criteria. Im trying to pass these
criteria whem I'm calling my own function.

for example I call my function with:

MyFunction TxtTotal, "[planned].[MyNumber]", "planned","WHERE
[planned].[weeknumber] = format(date(),'ww')"

My function then looks like:

Public Function MyFunction(MyOutput As TextBox, MyField As String, MyTable
As String, MyCriteria As String)

MyOutput = dsum(MyField, MyTable, MyCriteria)
msgbox Error 'just to check'

End Function

What am I doing wrong because I keep getting errormessage

Syntax error (missing operator) in query expression: 'WHERE
[planned].[weeknumber] = format(date(),'ww')'

I've tried placing semi-collons and collons everywhere (thinking that's the
problem) but nothing helps. I even tried datepart() instead of format() in my
criteria.

How do I pass on my variables to my function so that the built-in function
dsum() works?

Please help me!!!

BTW if I run an sql statement (like I use now as rowsource for a listbox):
"SELECT * from [planned] WHERE [planned].[weeknumber]= format(date(),'ww')"
it works perfectly!



Thanx!

Pain

 

[Marshall Barton]

what I'm trying to do is to create a universal function (stored in a module).

Inside the funtion i use the built-in function dsum to calculate something.
The dsum function needs a field, table and criteria. Im trying to pass these
criteria whem I'm calling my own function.

for example I call my function with:

MyFunction TxtTotal, "[planned].[MyNumber]", "planned","WHERE
[planned].[weeknumber] = format(date(),'ww')"

My function then looks like:

Public Function MyFunction(MyOutput As TextBox, MyField As String, MyTable
As String, MyCriteria As String)

MyOutput = dsum(MyField, MyTable, MyCriteria)
msgbox Error 'just to check'

End Function

What am I doing wrong because I keep getting errormessage

The criteria should not have the word Where in it.

The function would be more general if it returned the result
instead of assigning it to a specific text box.

Public Function MyFunction(MyField As String, _
MyTable As String, MyCriteria As String)
MyFunction = DSum(MyField, MyTable, MyCriteria)
End Function

Then call it from the text box's control source expression:

=MyFunction("MyNumber", "planned","weeknumber =
format(date(),'ww')"

BUT, notice that there is nothing more going on here than
using a different name for DSum. The test box could just as
well have used the expression:

=DSum("MyNumber", "planned","weeknumber =
Format(Date(),'ww')"

OTOH, maybe you have bigger plans for the function's future.

 

[Guest]

Thanx a lot for your help!

Stupid me, the word "WHERE" was causing all the problems. The reason I wrote
a function for this is because I want to call this function with different
variables (tables, criteria's), but the output always goes into 1 textbox.

what I'm trying to do is to create a universal function (stored in a module).

Inside the funtion i use the built-in function dsum to calculate something.
The dsum function needs a field, table and criteria. Im trying to pass these
criteria whem I'm calling my own function.

for example I call my function with:

MyFunction TxtTotal, "[planned].[MyNumber]", "planned","WHERE
[planned].[weeknumber] = format(date(),'ww')"

My function then looks like:

Public Function MyFunction(MyOutput As TextBox, MyField As String, MyTable
As String, MyCriteria As String)

MyOutput = dsum(MyField, MyTable, MyCriteria)
msgbox Error 'just to check'

End Function

What am I doing wrong because I keep getting errormessage

The criteria should not have the word Where in it.

The function would be more general if it returned the result
instead of assigning it to a specific text box.

Public Function MyFunction(MyField As String, _
MyTable As String, MyCriteria As String)
MyFunction = DSum(MyField, MyTable, MyCriteria)
End Function

Then call it from the text box's control source expression:

=MyFunction("MyNumber", "planned","weeknumber =
format(date(),'ww')"

BUT, notice that there is nothing more going on here than
using a different name for DSum. The test box could just as
well have used the expression:

=DSum("MyNumber", "planned","weeknumber =
Format(Date(),'ww')"

OTOH, maybe you have bigger plans for the function's future.