Return a formula to use in query

[PiB311]

Hi all.

This seems a little crazy to me, but I thought there had to be a way to do
this.

Here's the situation. I have 3 different types of data I work with. 1)
Volumes, 2) Handle Times 3) Costs.

I want to create a function that I can run in a query to be able to provide
a mathmatical formula for the data I need instead of running 3 separate
queries. I keep returning a string value.

The data has a few columns and I only want to use certain ones and provide a
single value without manually updating the query.

Metric ID Metric Type Volumes Start Time End Time
1 Cylce Time 100 12:00 pm
12:03 pm
2 Volume 100 12:38 PM
12:47 PM

if metric type = Cycle Time, I need the query to be as follows:

SELECT metric_id from tbl_metrics, calc_value AS ([End_Time] - [Start_Time]
FROM tbl metrics

I tried creating the following function:

function calc_type(int_type_id as int)
IF Type_ID = Cycle Time then
calc_type = [end_time] - [start_time]
End if

End Function

Please let me know if there is any way to do this.

Thank you in advance

 

[Allen Browne]

Your example function - Calc_Type() - accepts an integer, and returns a
Variant (since you did not specify a return type.) The Variant can be
anything, and can even change data types each time it's called, so the only
safe thing for JET to do is to treat it as Text. Then you have the problems
you experienced, where the data is treated as string values (for criteria,
sorting, or any further numeric operations you need.)

There are a couple of ways around this:

a) Change the query so it returns a number rather than a variant, e.g.:
Function Calc_Type(int_type_id as int) As Double

b) Create different functions that return the different data types, rather
than one function that is supposed to return them all.

c) Explicitly typecast the result in the query, e.g.:
CVDate(CalcType(1))

What I did not understand from your example, is how the function is supposed
to get the values from the record to work with. It seems to me that you
would need to pass in the values you want it to work with, e.g.:

Function calc_type(int_type_id as integer, start_time as Variant, _
end_time as Variant) As Long
IF Type_ID = 1 Time then
If IsDate(start_time) And IsDate(end_time) Then
calc_type = [end_time] - [start_time]
End If
End if
End Function