Using a boolean return value from a function as criteria in query

[Dietrich Schäffler]

Dear all,
I implemented a function "MyTestFunction (Value AS String) AS Boolean" in
the Module section of Access 2003 which returns either True or False (the
boolean constant) depending on the value passed to the function is in line
with some rules implemented in the function or not.

I added an extra row in the query definition grid "IsValid: MyTestFunction
([NAME])". If I execute the query the True and False results are shown
correctly (-1, 0) for all test values.

Now the query should display only the rows which fail the test. I inserted
the value False into the Criteria area - which is converted to Falsch as I'm
using the German version of Access. If I execute the query now, I receive
the error message 3464 (Type mismatch in criteria range ...). The same is
true if I use 0 (zero, without any quotes) as the criteria.

Any help is highly appreciated.

Best regards.
Dietrich Schäffler
HILTI AG
Schaan - Liechtenstein

 

[Allen Browne]

That approach should work.

Try declaring the function as:
Function MyTestFunction (MyValue As Variant) As Boolean

Several things (including fields and controls) have a value property, and
the String will fail if the value being passed is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Dietrich Schäffler]

Hi Allen,
thanks for the info: this solved my problem ... and now I'm able to delete
some work-around queries .-.;-).

Best regards,
Dietrich

That approach should work.

Try declaring the function as:
Function MyTestFunction (MyValue As Variant) As Boolean

Several things (including fields and controls) have a value property, and
the String will fail if the value being passed is Null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Dear all,
I implemented a function "MyTestFunction (Value AS String) AS Boolean" in
the Module section of Access 2003 which returns either True or False (the
boolean constant) depending on the value passed to the function is in line
with some rules implemented in the function or not.

I added an extra row in the query definition grid "IsValid: MyTestFunction
([NAME])". If I execute the query the True and False results are shown
correctly (-1, 0) for all test values.

Now the query should display only the rows which fail the test. I inserted
the value False into the Criteria area - which is converted to Falsch as
I'm
using the German version of Access. If I execute the query now, I receive
the error message 3464 (Type mismatch in criteria range ...). The same is
true if I use 0 (zero, without any quotes) as the criteria.

Any help is highly appreciated.

Best regards.
Dietrich Schäffler
HILTI AG
Schaan - Liechtenstein