pharasing my query request

[tiggy]

Hi I have a number of suppliers tariffs which show a rate per half or full kilo depending on the service, these normally go up to a given weight and then have a fixed amount per half or full kilo thereafter to manage this I have the following

=IIf([cboWeight]>20,DLookUp("Rate","qryExpressCourier","CountryID=Forms!frmCountry!cboCountry And Weight=20")+([Forms]![frmCountry]![cboWeight]-20)*DLookUp("Rate","qryExpressCourier","CountryID=Forms!frmCountry!cboCountry And Weight=-1"),DLookUp("Rate","qryExpressCourier","CountryID=Forms!frmCountry!cboCountry And Weight=Forms!frmCountry!cboWeight"))

To allow for weights above 20 to make this work I have an additional field in my table that is -1 and then the rate per kilo for that zone.

One of my suppliers though gives rates for weights up to 70.50 kilos in 9 different zone bands, zone r for example has individual rates for each half kilo from 0.5 to 70.5 and then has

71-99 kilos = 70.5 kilo rate (£62.16) + £1.20 per kilo
100-299 kilos = £110 + £1.10 per kilo
300-499 kilos = £330 + £1.10 per kilo
500-999 kilos = £500 + £1.00 per kilo
1000+ kilos = £1000.00 + £1.00 per kilo

I guess somehow that I am going to have to use < > but I am getting my knickers in a twist

Should each of these 'break' points be noted on my table? I can obviousely manage the 1st break in the same way as I have done on the simple tariff, that is by adding to my table zone a - weight -1 rate 1.20 but how do I then limit it to only going up to 99 kilos.

I figure that I am going to need a -2 rate to represent 100 - 299 kilos, a -3 rate to reprsent 300 - 499 kilos.

Can some kind soul please put me on the straight and narrow

Cheers


Tig

 

[EMP]

You can set up a lookup table with the fields [Country], [Wt_From], [Wt_To], [Formula] and use the Eval() function to evaluate the formula that is looked up.

I have attached an example in the database, which was saved from Access 2000. You can open the form, select a country and enter a weight. The amount will be calculated and displayed.

The code used is in the After Update Event of the text box txtWeight.

Hope it helps.

 

[tiggy]

That looks great, It will take me a while being new to find my way around why it works, if I get stuck is OK to toss a few questions at you?

Thanks

Tig

 

[tiggy]

Sorry, I have tied my self into a knot. My tables will have a country table with 3 fields

Country (196 Countries
Zone (9 zones)
Transit time

a Rate table

Zone (9 zones)
Weight_from
Weight_To
Rate

My form should react something like

Select Country: (unbound Combo looking up Country table)
Enter weight: (Text box?)
= Rate (Total amount) (From Rate table up to 70.50 kilos, calculated thereafter)
= Transit Time (taken from the Country table)

I have tried but my logic fails me. Would you please be so kind as to look at the zip and try to point me in the right direction.

TIA

Tig

 

[EMP]

I have moved the calculation of rate amount to a public function in a Module:-

Public Function getRate(sZone As String, Weight As Single) As Variant
  
  ' edit this line when rates for other zones are ready.
  If sZone <> "R" [b]And sZone <> "B"[/b] Then
    MsgBox "Rate table for zone " & sZone & " not yet ready"
    getRate = Null
    Exit Function
  End If
    
  Dim F As String
  ' lookup rate formula from table.
  F = DLookup("Rate", "tblFreightRates", "[Zone]='" & sZone & _
      "' and " & Weight & " BETWEEN [Weight_From] AND [Weight_To]")
  
  ' replace Wt in rate formula with numeric Weight entered.
  If InStr(F, "Wt") Then
    F = Left(F, InStr(F, "Wt") - 1) & Weight & _
        Mid(F, InStr(F, "Wt") + 2)
  End If
  
  ' evaluate formula and return amount.
  getRate = Eval(F)
     
End Function

I have added some rates for Zone B for testing. You have to check the rate table to see if I have interpreted the calculations correctly.

The function is used in the command button by passing it the values of Zone and Weight:-

Private Sub cmdFindAmount_Click()
  
  ' if country or weight is empty, display message and exit.
  If IsNull(Me.cboCountry) Then
    Me.cboCountry.SetFocus
    MsgBox "Select a country first."
    Exit Sub
  End If
  If IsNull(Me.txtWeight) Then
    Me.txtWeight.SetFocus
    MsgBox "Enter weight first."
    Exit Sub
  End If
  
  ' use getRate() function to get the amount.
  Me.txtAmount = getRate(Me.txtZone, Me.txtWeight)
  
End Sub

Whenever a country is selected/changed in the combo box, the corresponding Zone and Transit will be displayed:-

Private Sub cboCountry_Change()
  Me.txtZone = Me.cboCountry.Column(1)
  Me.txtTransit = Me.cboCountry.Column(2)
  Me.txtWeight = Null
  Me.txtAmount = Null
End Sub

The combo box contains the columns Country, Zone and Transit. The Transit column is suppressed by setting the ColumnWidths as 1.3";0.3";0". Note that in code the columns are numbered 0,1,2.

You can open the form, select a Zone R or Zone B country, enter a weight and click on the command button to return the amount.

 

[Rose412]

EMP-

I was looking for a way of doing something similar when I came to this thread. I found your code of evaluating a formula like (62.16 + 1.2*Wt) wonderful.

But I have a problem. Since the form is unbound, how do I make a permanent record by appending the data from the form to a table?

Thanks in advance.

Rose

 

[tiggy]

Dear EMP

How do you guys do this stuff, you must dream in codes etc. I am flabbergasted. Thank you for doing all the leg work for me I had not expected that but it is great. I have added all my zones, countries rates etc. and it works great, I do not yet understand the mechanics but hopefully I will get there.

Thanks again

Steve

 

[EMP]

Steve,

Thank you for doing all the leg work for me I had not expected that

I simply found it easier to show an example than set out all the steps in words. Besides, you still have to add your own code to handle possible errors in user input.


Rose,

You can use an append query with a Values List.

Using Steve's sample, we can first create a table tblAmount with the fields Country, Zone, Transit, Weight, Amount and then use the following append query to post the data from the form to the table:-

INSERT INTO tblAmount ( Country, Zone, Transit, Weight, Amount )
VALUES ([forms]![Freight Rate]![cboCountry], [forms]![Freight Rate]![txtZone], [forms]![Freight Rate]![txtTransit], [forms]![Freight Rate]![txtWeight], [forms]![Freight Rate]![txtAmount]);

 

[Rose412]

Thank you very much.

I was familiar with the syntax:-
Insert Into ... Select ... From ....

I did not notice that Insert Into can be used with a "Values List", too.

Thanks again.