Calculate Total (1 Viewer)

[Gismo]

Hi all,

I have a form where I select the product
If the product has a unit of measure as Kg, the i need to multiply Mass * Unit price
If not Kg then Qty * Unit price

What is the proper way to do these calculating with an If statement
Not sure if this can be done with an unbound control and using the builder

 

[theDBguy]

Maybe something like:

=IIf([Unit of Measure]="Kg",[Mass]*[Unit Price],[Qty]*[Unit Price])

 

[The_Doc_Man]

At some point there will have to be an explicit or implicit flag that says "mass" is the unit (whether pounds, kilograms, ounces, or tons).

If there is only one "mass" unit case, use an IIF expression. If there is more than one, then use SWITCH.

Switch Function - Microsoft Support

Use the Switch function in Acess to evaluate a list of expressions.

support.microsoft.com

 

[Gismo]

Maybe something like:

=IIf([Unit of Measure]="Kg",[Mass]*[Unit Price],[Qty]*[Unit Price])

Thank you

What would be the correct approach to accomplish the desired result?
I used this formula in the underlying query but gives me a null result

 

[June7]

Perhaps you should tell us the data structure - maybe provide your db for analysis.

 

[Gismo]

Perhaps you should tell us the data structure - maybe provide your db for analysis.

Basically all then needs to happen is when I select the product from die drop down box, the qty and or mass is entered with the unit price
If the mass is entered, i need mass * unit price, if no mass the qty * unit price

initially i based my query if UOM is KG then the above should apply but I might have other units where a mass or volume should be entered to can not only refer to "KG"

I need to do the same with the UOM to be updated after the product has been selected but getting wright conflict so I need to look at another approact to update the control whien same record is still in focus

 

[June7]

Will a record have both mass and qty entered? If not and mass and qty are mutually exclusive, why have two fields?

 

[Gismo]

Will a record have both mass and qty entered? If not and mass and qty are mutually exclusive, why have two fields?

Qty and mass could be either just qty or qty and mass
Not all qty will have a mass
Sample
bacon 200g pack will only have qty 1 and no mass as you purchase 1 pack at a price per pack
Fresh meat you would purchase per kg but still I would add a qty of the amount of say 1 beef fillet
Everything comes pre packed

 

[June7]

"If the mass is entered, i need mass * unit price, if no mass the qty * unit price". That's simple to accomplish, doesn't matter what the unit is.

[Unit Price] * IIf(IsNull([mass]), [qty], [mass])

Advise not to use space in naming convention.

 

[Gismo]

"If the mass is entered, i need mass * unit price, if no mass the qty * unit price". That's simple to accomplish, doesn't matter what the unit is.

[Unit Price] * IIf(IsNull([mass]), [qty], [mass])

Advise not to use space in naming convention.

May I please ask you to assist in extending this formula
Not sure what the contention should be

[Unit Price] * IIf(IsNull([mass]), [qty], [mass])

I also need to add that when the currency is UGX then same formula applies
If the currency is not UGX then it should be Qty * unit price * exchange when mass is null
or if the currency is not UGX and the mass is not null the mass * unit price * exchange

Hope this makes sence

 

[MajP]

Untested

iif([currencyField] = "UGX", nz([mass],1) * nz([quantity],1) *[UnitPrice], nz([mass],1) * nz([quantity],1) *[UnitPrice] * [Exchange])

Nz([Mass],1) returns 1 if null else the value for mass

 

[Gismo]

Untested

iif([currencyField] = "UGX", nz([mass],1) * nz([quantity],1) *[UnitPrice], nz([mass],1) * nz([quantity],1) *[UnitPrice] * [Exchange])

Nz([Mass],1) returns 1 if null else the value for mass

Wow, thank you so much, works great

 

[Gismo]

May I please use the same post to ask a different question?

Private Sub Form_Current()
If MassRequired = True Then
Mass.TabStop = False
Else
Mass.TabStop = True
End If
End Sub

Does this mean that if Mass required is true that Mass control will be active
If mass require is false, Mass control will not be active

Does not seem to work as required

Currently the Mass tab stop is set to yes

whatever I change the true and False to it either goes to het control or it tabs to the next control

 

[MajP]

One thing you can write this type of code as a toggle, which makes it easier to write. I would try enabled.
Mass.enabled = massrequired

if massrequired is true Mass control is enabled, if false not enabled


FYI I normally would have two fields instead of mass and quanity to give me more flexibility and simplify this.
Amount
UnitMeasure

Amount would be numeric
UnitMeasuer would be values of "Kg", "lbs","Liters", "Miles" etc or "Each/Unit"
With this you can set up conversion tables to convert anything to a base measure such as grams to Kg, or ounces to liters.
Now simply make both fields required.
You are then always [Amount] * [UnitPrice]

 

[June7]

Now you are multiplying mass * quantity? So if both fields have data (which you said is possible) do you get the desired output?

 

[Gismo]

Now you are multiplying mass * quantity? So if both fields have data (which you said is possible) do you get the desired output?

No, Qty and mass should never be multiplied together
Am I missing something in the formula?

 

[June7]

MajP's suggestion is doing just that. You said it works great. You also said it is possible for data to be in both fields. So if data is in both mass and qty fields, they will be multiplied.

Consider:

[unit price] * IIf([currency] <> "UGX", [exchange], 1) * IIf(IsNull([mass]), [qty], [mass])

 

[Gismo]

One thing you can write this type of code as a toggle, which makes it easier to write. I would try enabled.
Mass.enabled = massrequired

if massrequired is true Mass control is enabled, if false not enabled


FYI I normally would have two fields instead of mass and quanity to give me more flexibility and simplify this.
Amount
UnitMeasure

Amount would be numeric
UnitMeasuer would be values of "Kg", "lbs","Liters", "Miles" etc or "Each/Unit"
With this you can set up conversion tables to convert anything to a base measure such as grams to Kg, or ounces to liters.
Now simply make both fields required.
You are then always [Amount] * [UnitPrice]

Hi
I have Qty, mass and UOM
If mass is not required as per the product master, then mass control should not be enabled
TabStop does not seem to work in this case, unless I am not using the code correctly

No matter what I do with the code, it either goes toe this control or skips this control no patter what the mass required control displays

 

[Gismo]

MajP's suggestion is doing just that. You said it works great. You also said it is possible for data to be in both fields. So if data is in both mass and qty fields, they will be multiplied.

Consider:

[unit price] * IIf([currency] <> "UGX", [exchange], 1) * IIf(IsNull([mass]), [qty], [mass])

Yes, I apologies, still early in the morning
It is possible to have both qty and mass
I will keep the formula as is

 

[Gismo]

One thing you can write this type of code as a toggle, which makes it easier to write. I would try enabled.
Mass.enabled = massrequired

if massrequired is true Mass control is enabled, if false not enabled


FYI I normally would have two fields instead of mass and quanity to give me more flexibility and simplify this.
Amount
UnitMeasure

Amount would be numeric
UnitMeasuer would be values of "Kg", "lbs","Liters", "Miles" etc or "Each/Unit"
With this you can set up conversion tables to convert anything to a base measure such as grams to Kg, or ounces to liters.
Now simply make both fields required.
You are then always [Amount] * [UnitPrice]

Still des not seem to have the require effect, not sure if I am missing something here

Private Sub Form_Current()
If MassRequired = True Then
Mass.Enabled = True
Else
Mass.Enabled = False
End If