a formula that can be used to calculate delivery charge

[ashni]

hey guyz,
im buildin a database.
in 1 of my forms i need to calculate the delivery charge accordin to the weight of the items.
i have the followin fields that im using- itemprice, itemweight,deliverycharge.
if the itemweight is below 10 then 5 should be charged and if above 10 then 5+5%of the itemprice should be charged.
is it possible to have a formula to do this?
please help me.
thank you

 

[missinglinq]

And if itemweight is exactly 10 what do you want to charge?

 

[ashni]

if the weight is 10 then 5 should be charged

 

[DCrake]

Public Function DeliveryCharge(Price As Currency, Weight As Double,Charge As Integer,OnCost As Double) As Currency

If Weight < 10 Then
   DeliveryCharge = Charge
Else
   DeliveryCharge = Charge + ((Price/100) * OnCost)
End If

End Function

In your query you would calculate the deliverycharge as Follows

Delivery:DeliveryCharge([UnitPrice],[itemWeight],5,5)

The 3rd and 4th arguments have been added to function so that any changes to the variable parameters will not involve any recording as the function handles the factors.

 

[ashni]

i tried, its not workin, is there any other way?

 

[JANR]

Not working isen't helpful. What isen't working??

I'v tested it and it work as it suppose to do, with a slight oversight fromt DCrake.

Public Function DeliveryCharge(Price As Currency, Weight As Double,Charge As Integer,OnCost As Double) As Currency
 
If Weight <[COLOR=red]=[/COLOR] 10 Then
   DeliveryCharge = Charge
Else
   DeliveryCharge = Charge + ((Price/100) * OnCost)
End If
 
End Function

Delivery:DeliveryCharge([[COLOR=red]Item[/COLOR]Price],[itemWeight],5,5)

But with a little efford you could have figured it out yourself.

JR

 

[ashni]

well im copy pasting the code in the DeliveryCharge field but it aint working.
is there any other way?basically i paste the code in the code builder buh my other fields are now not workin...
can i retype how my system is workin?maybe i left out somethng...
i have the following fields, ItemPrice,ItemWeight,QuantityOrdered
then these fields include calculation, the fieldd are: TotalWeight, TotalCost....they are calculated by multiplyin Itemweight*QuantityOrdered and ItemPrice*QuantityOrdered......
so basically my deliverycharge will be based on TotalWeight and TotalCost...
thank you..

 

[highandwild]

Surely the calculation in the query should be :

DeliveryeliveryCharge([ItemPrice]*[QuantityOrdered], [Itemweight]*[QuantityOrdered],5,5)

and put the following code in a code module:

Public Function DeliveryCharge(Price As Currency, Weight As Double,Charge As Integer,OnCost As Double) As Currency

If Weight <= 10 Then
DeliveryCharge = Charge
Else
DeliveryCharge = Charge + ((Price/100) * OnCost)
End If

End Function

 

[ashni]

thank you...
im trying to make it work, hope it does...thanx

 

[highandwild]

Let me know how you get on.

 

[ashni]

there no value comin on the field, iv put the code...
is it because iv not made a query or?coz im not plannin to have a query for tht.,..leme knw thnx

 

[highandwild]

Can you post a database with at least the table in it, the query and the code module and anything else that is needed.

 

[ashni]

Order TableOrderIDCustomer IDStockIDHamperTypeDestinationOrderDateDeliveryDateItemNoCountryOfOriginQuantityOrderedItemWeightItemPriceStandardHamperPriceDeliveryChargeTotalWeightTotalCostAmountPayableStockLevelReorderLevelO1000C1000S1000Standard HamperUnited Kingdom12/03/201013/03/20102001Denmark1

£60.00£0.00


10
O1001C1001B1001Bespoke HamperUnited Kingdom17/03/201018/03/20101006Andorra21.15£12.00
£5.00


89
O1002C1002B1002Bespoke HamperRest of Europe23/04/201026/04/20101007Austria112£6.00
£0.00


67
O1003C1003S1003Standard HamperRest of Europe22/04/201025/04/20102002Germany1

£60.00£0.00


10
O1004C1004S1004Standard HamperRest of Europe02/05/201005/05/20102003Belgian2

£60.00£0.00


10
O1005C1005B1005Bespoke HamperRest of Europe07/05/201010/05/20101008France41.5£8.00
£0.00


76


that is the copy of my table, i dnt have a query for it....dint think it is necessary to have a query....

 

[highandwild]

Look at the attached for ideas.

 

[ashni]

heyy thanx alot, it worked...it got it how to do it..
realy appreciate it...thank you

 

[highandwild]

I'm glad it worked.

Using Access to good effect is to some extent about combining lots of different techniques and you have just two of them here.