Return a formula to use in query

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

.

Relevant Pages

  • Re: Return a formula to use in query
    ... Your example function - Calc_Type- accepts an integer, and returns a Variant 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. ... Change the query so it returns a number rather than a variant, ... if metric type = Cycle Time, I need the query to be as follows: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Average of Cycle Time Per Month
    ... Greek 'sigma' character on it to convert the query to a Totals query. ... Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA ... Lot# DateLotRecvd DateLotInDeptA DateLotOutDeptA Cycle Time ...
    (microsoft.public.access.queries)
  • Re: Specified Expression/Aggreagate Function
    ... The SQL post is as follows: ... I used Design view to create the query. ... Date Part Number Tool Number Cycle Time ... I want the query to calculate Expr 1 for EACH of the Tool Number ...
    (microsoft.public.access.queries)
  • Re: Specified Expression/Aggreagate Function
    ... "Bob Barrows" wrote: ... Sum() is an aggregate function designed to sum values contained in ... returned in a query, it can be used without a GROUP BY clause: ... Date Part Number Tool Number Cycle Time ...
    (microsoft.public.access.queries)
  • Re: Specified Expression/Aggreagate Function
    ... query: "You tried to execute a query that does not include the ... psecified expression 'Part Number' as part of an aggregate funtion". ... Date Part Number Tool Number Cycle Time ... I want the query to calculate Expr 1 for EACH of the Tool Number ...
    (microsoft.public.access.queries)