Calculations

[Nick Toye]

Hi,

I need to add an expression to a form that calculates another cells information. By the way I am working for a courier company.

For example.

In the cell for weight I put 50 in.

I then want the price cell to work out a cost based on that figure.

But I have different prices based on what the weight is. For example if the weight is 58, it costs 5.50 for 0-30 kilos. Then if it is over it costs 18pence per kilo thereafter. So the final figure would come to 11.10.

How would I write this as an expression in my form. I want it to automatically produce a price based on what weight I put in it.

Many thanks in advance.

Nick Toye

 

[crosmill]

Right clikc the text bax you want he answer in, build event| code bulider.

Dim Value int

'Set value from entered eg. 50
Value = Me!FieldName

If Value < 50 Then
me.ResultFieldName = Value * 4
End If


If Value BETWEEN 51 AND 60 Then
me.ResultFieldName = Value * 5
End If

 

[Mile-O]

I have different prices based on what the weight is. For example if the weight is 58, it costs 5.50 for 0-30 kilos. Then if it is over it costs 18pence per kilo thereafter. So the final figure would come to 11.10.

Where are you getting £11.10 from? My maths has the value at £10.54

where the weight is 58, the first 30kg cost £5.50

then each additional kilogram adds a further £0.18 to the overall price

so 58kg - 30kg = 28kg


For each of these 28kg we add £0.18 - 28*0.18 = 5.04

So £5.04 + £5.50 = £10.54

Or have I not grasped what you are wanting to do?

The formula you'd want is:

=IIf([Weight]<=30,5.5,5.5+(([Weight]-30)*0.18))

 

[Nick Toye]

Thanks for that, thats really useful.

I actually put down 0.18 when I wanted 0.20 which actually gives me 11.10, I got confused.

However,

The price matrix works like this

Zone 1:
0-30 = £5.50
31 - 99 = 0.20 per kilo thereafter
100+ = 19.50 + 0.18 per kilo thereafter 100 kilos

Pallets = £50

Zone 2:
0-20 = £5.50
21 - 00 = 0.22 per kilo thereafter
100+ = 23.10 + 0.20 per kilo thereafter 100 kilos

Pallets = £58

I have a cell in my form for zones which I can enter 1 or 2.

Is this going to be a massive formula, or just a nice challenge for you.

Many thanks in advance, i'm new to access but I feel that i'm learning so much every day.

Again thanks in advance for your help.

Nick

 

[Mile-O]

Don't know if I've understood totally but managed this:

=IIf([Zone]=1,IIf([Weight]<=30,5.5,IIf([Weight]<=99,5.5+(([Weight]-30)*0.2),5.5+((99-30)*0.2)+(([Weight]-99)*0.18))),IIf([Weight]<=20,5.5,IIf([Weight]<=100,5.5+(([Weight]-30)*0.22),5.5+((100-30)*0.22)+(([Weight]-100)*0.2))))

 

[Nick Toye]

I think you may well have missed out a bit of code for the zone 2. Also where do I enter this code, in the control source box on the properties?

 

[Mile-O]

Zone 2:
0-20 = £5.50
21 - 00 = 0.22 per kilo thereafter
100+ = 23.10 + 0.20 per kilo thereafter 100 kilos

Pallets = £58

Probably because Zone 2 seems a bit unclear.

21- 00 ?

100 + = 23.10 +0.20 per kilo

does that mean for 101kg and upwards the initial charge of 5.50 + 0.22 per kilo is abandoned, and this new set price of 23.10 + 0.20 comes in?

 

[Nick Toye]

Yes sorry for being unclear.

Its like this:

Zone 1:

0 - 30 kilos = £5.50
31 - 99 kilos = Plus 20p per kilo thereafter
100 kilos plus = £19.50 per 100 kilos Plus 18p per kilo thereafter
Pallet = £50 per pallet

Zone 2:

0 - 20 kilos = £5.50
21 - 99 kilos = Plus 22p per kilo thereafter
100 kilos plus = £23.10 per 100 kilos Plus 20p per kilo thereafter
Pallet = £58 per pallet

I need to have the pallets put in as well. Is this getting to complicated? I am also trying to understand it and trying formulas but I keep hitting a brick wall.

Again, thanks for your help so far I really appreciate it.

 

[Mile-O]

Rather than write a big, ridiculously long expression - it may be more prudent to write a function in VBA that will return an answer for you.

I still don'e get the relevance of the pallets - are they a separate field that, for each one, you add to the overall total.

If so, you can amend this function:

Function GetCost(ByVal intZone As Integer, ByVal intWeight As Integer) As Currency

    If IsNull(intZone) Then
        GetCost = 0
        Exit Function
    End If
    
    If IsNull(intWeight) Then
        GetCost = 0
        Exit Function
    End If
    

    Select Case intZone
        Case Is = 1
            Select Case intWeight
                Case Is <= 30
                    GetCost = 5.5
                Case Is < 100
                    GetCost = 5.5 + ((intWeight - 30) * 0.2)
                Case Else
                    GetCost = 19.5 + ((intWeight - 99) * 0.18)
            End Select
                
        Case Is = 2
            Select Case intWeight
                Case Is <= 20
                    GetCost = 5.5
                Case Is <= 99
                    GetCost = 5.5 + ((intWeight - 20) * 0.22)
                Case Else
                    GetCost = 23.1 + ((intWeight - 99) * 0.2)
            End Select
        Case Else
            GetCost = 0
    End Select

End Function

As it is now, just call it like this:

=GetCost([Zone],[Weight])

 

[Nick Toye]

That works brilliant, many thanks mate.

The pallet is a seperate field, for example if there is 1 pallet, than nothing will be entered in the weight field.

Its like a one of cost, so no weight details are taken if there is a pallet, except if there is loose packages along with the pallet, therefore there would be weight to enter in the weight field, thus it will be added to the cost.

Thanks again, your a great help.

 

[Nick Toye]

I've noticed another thing too,

When I look in my table format of the form, the prices are not showing. It shows all the right zones entered and the weights, but for price it shows zero

Do you know why that is?

 

[Nick Toye]

How do I update the table that the form is based on. The fields for price remain at zero. I have been informed that this is because it is bound. I need to be able to print off the various delivery notes within a certain date span as one invoice, but the calculated figures don't show up anywhere but in the form.

Please help, i'm fastly approaching a deadline and I can't seem to find the right answer.

Thanks

 

[Pat Hartman]

Please don't post the same question multiple times. What about all the answers you have in your other post?