darn function criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Pain said:
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
Click to expand...


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.
 
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.

Marshall Barton said:
Pain said:
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
Click to expand...


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.
Click to expand...
 
Back
Top