Function In Query Criteria

[Steve]

I need help with a function to be used as the criteria for a query. The field in
the query is InventoryStatus:[Inventory] - [ReorderPoint]. The function is built
around a Select Case set of about twelve cases.

Function MyCriteria()
Select Case Forms!MyForm!MyCbx
Case 1
My Criteria = "<0"
Case 2
MyCriteria = "0"
Case 3
My Criteria = "Between 1 And 3"
Case 4
My Criteria = "Between 1 And 6"
etc
End Select
End Function

The above doesn't work!

Thanks for all help!

Steve

 

[Trevor Best]

I need help with a function to be used as the criteria for a query. The field in
the query is InventoryStatus:[Inventory] - [ReorderPoint]. The function is built
around a Select Case set of about twelve cases.

Function MyCriteria()
Select Case Forms!MyForm!MyCbx
Case 1
My Criteria = "<0"
Case 2
MyCriteria = "0"
Case 3
My Criteria = "Between 1 And 3"
Case 4
My Criteria = "Between 1 And 6"
etc
End Select
End Function

The above doesn't work!

You might want to consider writing the entire SQL into the querydef
object and saving it each time if it's your goal to make the query
behave differently each time. If you're after opening a report with a
slightly different criteria then consider using the where clause
argument on the OpenReport method.

 

[Michel Walsh]

Hi,


Alternatively:



WHERE Choose( Forms!MyForm!MyCbx, (Inventory-ReorderPoint) <0,
(Inventory-ReorderPoint)=0, (Inventory-ReorderPoint) BETWEEN 1 and 3,
(Inventory-ReorderPoint) Between 1 and 6 )



Note that Choose is a function, not a statement, and thus, each of its
argument need to be evaluated.

Hoping it may help,
Vanderghast, Access MVP

I need help with a function to be used as the criteria for a query. The field in
the query is InventoryStatus:[Inventory] - [ReorderPoint]. The function is built
around a Select Case set of about twelve cases.

Function MyCriteria()
Select Case Forms!MyForm!MyCbx
Case 1
My Criteria = "<0"
Case 2
MyCriteria = "0"
Case 3
My Criteria = "Between 1 And 3"
Case 4
My Criteria = "Between 1 And 6"
etc
End Select
End Function

The above doesn't work!

You might want to consider writing the entire SQL into the querydef
object and saving it each time if it's your goal to make the query
behave differently each time. If you're after opening a report with a
slightly different criteria then consider using the where clause
argument on the OpenReport method.

 

[Edward]

I need help with a function to be used as the criteria for a query. The field in
the query is InventoryStatus:[Inventory] - [ReorderPoint]. The function is built
around a Select Case set of about twelve cases.

Function MyCriteria()
Select Case Forms!MyForm!MyCbx
Case 1
My Criteria = "<0"
Case 2
MyCriteria = "0"
Case 3
My Criteria = "Between 1 And 3"
Case 4
My Criteria = "Between 1 And 6"
etc
End Select
End Function

The above doesn't work!

Thanks for all help!

Steve

For correctness (though not in this instance a dealbreaker) you should
declare the return type of the function e.g.

Function MyCriteria() As String

Otherwise, I can't see anything wrong. How does it "not work"?

Edward

 

[Trevor Best]

Hi,


Alternatively:



WHERE Choose( Forms!MyForm!MyCbx, (Inventory-ReorderPoint) <0,
(Inventory-ReorderPoint)=0, (Inventory-ReorderPoint) BETWEEN 1 and 3,
(Inventory-ReorderPoint) Between 1 and 6 )



Note that Choose is a function, not a statement, and thus, each of its
argument need to be evaluated.

I'm not bothering to check there but are you sure? IIf() is a function
and when used in VBA all parameters are evaluated but if used in an
expression in a form or query it doesn't evaluate all parameters, it
works differently to VBA.

 

[Michel Walsh]

Hi


That is because there are two individuals sharing the same name, iif. In
Jet-SQL, iif is a statement, while in VBA, iif is a function. " If then
elseif else endIf ", in VBA is a (complex) statement. A statement is like a
control of the flow of execution. Not all parts of the control flow need to
be evaluated (like an unconditional GO TO that skips other statements and
functions ). A function, on the other hand, always evaluate its argument
even before beginning its own execution. As example, with

Abs( 4 + 5 )

the internal working of Abs will just see 9, not 4+5, and would not be able
to differentiate that 9 from another 9 coming from 12-3, as example. Abs( )
is a function. All the arguments are evaluated, then, the function is
called. You can trace that with

MyFunction3( MyFunction1() + MyFunction2( ) )

and you will see that MyFunction1 and MyFunction2 are called before
MyFunction3.

iif( ) - in VBA - is a function, so, each argument have to be evaluated
before iif starts. That is not a "special" rule, it is the general rules for
all functions.


iif( ) - in Jet SQL - is a statement, we cannot really speak of "argument"
here, but definitively, just the required "parts" are evaluated, as in any
other statements.


Choose( ) and Switch( ) are only defined in VBA, not in Jet SQL, and are
functions, not statements. As a side effect, you cannot use them from a C++
with Jet, or VB6 with Jet, just from a Access+Jet combo.



Hoping it may help,
Vanderghast, Access MVP