Creating a sum of different units

[Guest]

Hello,

I am working on a project where I need to create a sum of different units. I
have created a sub form containing data that is calc. in different units.

i.e.
bottle #1 1 Kg
bottle #2 500 mg
bottle #3 1 g

Sum of all bottles = 1001.001 grams

Sometimes the measures are in mass (grams) or in Volume (liters).

Does anyone have any suggestions? Thank you so much for reading this!

Jami

 

[J. Goddard]

Best idea is to use a the same unit for all. Failing that, you could
use another field to indicate what the units are, and convert to the
common unit as required (reports, queries).

Summing when some are mass and some are volume makes no sense (IMHO);
that's like adding Euros and dollars without converting either one - the
result is meaningless.

John

 

[Guest]

Thank you John for responding to my post.

I'd rather not ask the user to convert since my thought is that this should
be a relatively easy thing to do in VBA - perhaps a case statement using a
variable that would sum the values. The chemicals they are using are in
either very small quantities i.e ml or larger quantities kilograms.

I'd be either using ALL mass or ALL volume for each measured record not both
within one. I agree this would not make any sense! So I'd like to have
code to handle the conversation of either.

Thank you again,

Jami

 

[J. Goddard]

Hi -

You needn't make the users do the conversion. All you need is for them
to enter the quantity, and then select the units. This could be from a
combo box or an option group if there are not too many choices. You
could put the conversion code in the After Update event of the units
control.

Anothr option could be used to select liquid or mass measures, and
change the unit selection accordingly.

A couple of little extras that I can think of:

Put a "none" option in the Units control, and default it to that, so
that users are required to select a unit. Check for "None" in the After
Insert event of the form.

If a user changes the amount, set the units box back to "None" - you
don't know what they may have in mind.

All this assumes that you have one bottle per record, if this is not the
case, you'll have to do this differently, but the concept is the same.

Hope this helps

John