perform different calculations depending on selected item from a look up field

[sslater]

I am seeking suggestions on how to accomplish the following. I would like to perform different calculations depending on the value selected from a look up field. Essentially a conversion depending on the unit of measure.



For example, if inches is selected perform the calculation based on the thick, length, width, etc fields within the same table or form and place the value in a seperate field. If mm is selected perform a different calculation and place it in the field, and so on.

I have attempted to do so using VBA and macros with no success and much frustration I have been using access for a few months so any suggestions are greatly appreciated.

Thx
losinghermind!

[ssanfu]

I never use look up fields, MVFs or calculated fields.

One way would be to write a user defined function (UDF).
A function generally is used to return a value, where a sub does not.

You mentioned "Inches" and "mm" (we'll call them "Units"). In the function (lets call it "UnitCalcs")m you would have to have calculations for each "Unit" type.
You pass the unit type to the function and in the function have a Select Case construct to pick which calculation to do.

Function UnitCalcs (pUnitType as string) as Double

This says: there is a function named "UnitCalcs" that takes a string parameter and returns a number of type Double. (I use the prefix "p" for parameter - as in "pUnitType")

Then you have a Select Case:

Dim Result as Double

Select Case pUnitType
   Case "Inches"
       'do some calculations here
       Result =  1 * 2
   Case  "mm"
      ' do some calculations here
      Result =  3 * 4
   Case Else
      'in case something in not recongised
      Result = -1111  'what I use to tell me an error occurred
End Select

Then you have to set the return value. You use the name of the function:

UnitCalcs = result

So the UDF looks like:

Function UnitCalcs (pUnitType as string) as Double
Dim Result as Double

Select Case pUnitType
   Case "Inches"
       'do some calculations here
       Result =  1 * 2
   Case  "mm"
      ' do some calculations here
      Result =  3 * 4
   Case Else
      'in case something in not recognized
      Result = -1111  'what I use to tell me an error occurred
End Select

UnitCalcs = result

End Function

Obviously, there needs to be more to this function, but this is the general idea.

Now you can use this UDF from anyplace in the dB by passing a parameter that the UDF is expecting. The UDF would need to be able to get the required values for the calculations or you could pass the thick, length, width as parameters along with the units.
You could use the UDF in a text box:

=UnitCalcs("inches")

or in a query:

SELECT field1, field2, MyCals: UnitCalcs("mm") 
FROM MyTable

You might also just do the calculation in a query, if the calculation is simple enough. If the thick, length, width were in the record, in a query you cluld use:

SELECT thick, length, width, Vol: thick * length * width 
FROM MyTable

It really depends you what your requirements for the calculations are.


Does this help??

[sslater]

I will try the UDF you suggest as I would prefer to leave options to add calculations later, silly question though.....where do I put the code and on which event or does it matter?

Your advice is greatly appreciated, hope you don't mind questions along the way as I may have many!

[ssanfu]

where do I put the code

I would put the function in a standard module. Maybe name the module "modCalculations". Don't name the module the same name as a subroutine.

on which event or does it matter?

No event. Just a standard function. You could put the code in a form module, but then the function is only visible to that form. In a standard module, you can access it from any form.

hope you don't mind questions along the way as I may have many!

Questions are how we learn.....


And to the forum...

[sslater]

Can you explain the result line (result = 1 * 2) in your example?

This will be a first, creating a module that is. Here goes nothing.......

[ssanfu]

I didn't know what your calculation was or how many, so I just multiplied 1 and 2 to demonstrate.
What is the calculation for "Inches"?

[sslater]

So far I have,
Function UnitCalcs(pUnitType As String) As Double
Dim result As Double


Select Case pUnitType
Case "CfInches"
CfInches = [Thickness] * [Width] * [Length] * [Qty] / 1728
Case "CfMetric"
CfMetric = [Thickness] * [Width] * [Length] * [Qty] / 1728 / 16387.064
Case "SfInches"
SfInches = [Width] * [Length] * [Qty] / 144
Case "SfMetric"
SfMetric = [Width] * [Length] * [Qty] / 92903.04
Case "LfInches"
LfInches = [Length] * [Qty] / 12
Case "LfMetric"
LfMetric = [Length] * [Qty] / 304.8

[ssanfu]

Close...

Option Compare Database
Option Explicit

Public Function UnitCalcs(pUnitType As String) As Double
    Dim result As Double

    Select Case pUnitType
        Case "CfInches"
            result = ([Qty] * [Length] * [Width] * [Thickness]) / 1728
        Case "CfMetric"
            result = ([Qty] * [Length] * [Width] * [Thickness]) / 1728 / 16387.064
        Case "SfInches"
            result = ([Qty] * [Length] * [Width]) / 144
        Case "SfMetric"
            result = ([Qty] * [Length] * [Width]) / 92903.04
        Case "LfInches"
            result = ([Qty] * [Length]) / 12
        Case "LfMetric"
            result = ([Qty] * [Length]) / 304.8
    End Select

    'return result of calculation
    UnitCalcs = result

End Function

The result of the calculation must be the same variable, not the pUnitType.

I rearranged the dimensions - every calculation has length and quantity.
4 have length, quantity and width. Constant order of the arguments makes it easier to remember which value goes where.

Are [Qty] , [Length], [Width] and [Thickness] controls on a form?

[sslater]

All are a text box on a form.

[ssanfu]

I might have lead you down the wrong path. Not that the function was wrong - it would take more parameters.
(trying to do too many things at once; have a computer that is dying and trying to save it....)

Try this demo.

[sslater]

Unfortunately I don't think that will work. After data entry, the result of the calculation needs to populate a field. One record will show multiple calculations. Can you continue to assist with the previous method even though it will take more parameters?

Thanks for your help so far, greatly appreciated.

[ssanfu]

One record will show multiple calculations.

What is the table structure? Doesn't sound normalized....

Can you continue to assist with the previous method even though it will take more parameters?

I will try..


Can yo post your dB?

[sslater]

I can via email. If that works send me an email and I will forward.