Calculating Postal costs on a report (1 Viewer)

drazen · Sep 20, 2017

Hi, I have a report with a list of parcels and their weights, I am trying to get the report to calculate the postage cost based on the weight. I am using VBA to do it, but I just can't get it to work, this is the code I have.

me.weight is a bound field and me.postage is an unbound text box

Code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.Weight < 0.1 Then Me.postage = 0.56
If Me.Weight > 0.1 And Me.Weight < 0.25 Then Me.postage = 1.22
If Me.Weight > 0.25 And Me.Weight < 0.5 Then Me.postage = 1.58
If Me.Weight > 0.75 And Me.Weight < 2 Then Me.postage = 2.82
If Me.Weight > 2 And Me.Weight < 5 Then Me.postage = 7.19
If Me.Weight > 5 And Me.Weight < 10 Then Me.postage = 7.56

End Sub

Can anyone help?
Thanks

Minty · Sep 20, 2017

I would probably put your postage weights and rates into a table, then use a function to return the rate in the reports underlying query.

Two reasons for doing it this way - firstly if the rates or weight breaks change you simply edit one table, not lumps of code dotted all over the place, and secondly you can build a form to let someone else do the editing - if it's hard coded you have to change it.

The table can be simple, only two or three fields required I think;

Code:

WeightPoint, Rate, DateChanged
0,1      0.56     1/9/2017
0.25       1.22     1/9/2017
etc.

Then the function simply looks up the first rate that is = or less than than the weight you pass in.

jdraw · Sep 20, 2017

Just to say I agree with Minty. Do not hard code values that are subject to change. You could get new postal rates at any time, so a reference table with values and effective dates offers flexibility.

In some cases postal rates may be subject to size (I think).
Consider, (adjust this example to make the concept more obvious) a 1 lb box of chocolates vs a 1 lb piece of 1/2 " thick styrofoam. Just saying there may be additional parameters to your rates table???

I have been in US Post offices where a package must fit within a template, and, if it doesn't, another rate applies.

drazen · Sep 20, 2017

Thanks for the reply's, I'm not all that knowledgeable with Access, But I'm ok with VBA.
I do have a table with the Postal rates on, they fill a sub-form on the main form where the report button is. The parcels are all very similar sizes, its just parcels of papers.

I guess I'm more comfortable with VBA, If I could get the code above working then I would exchange the hard code postal rates with links to the sub-form. I know its not the best solution, but I would understand it.

Minty · Sep 20, 2017

If you post up a small sample database with the basic forms, some sample data and your rate table it should be simple to demonstrate what we are describing.

drazen · Sep 21, 2017

Thanks for the help. I'm not sure I could pop up a small database. I'm not that good with access. I'll have to find another way around it.

Thanks anyway

Minty · Sep 21, 2017

All we would need is probabaly two tables - some sample "Mail items" and your rate chart.

drazen · Sep 21, 2017

Hi, ok, I think i've managed to do a small database. Hope this is what you want.
Thanks so much for this.

Minty · Sep 21, 2017

Have a look at the reports query. It is a simple way of demonstrating a cross join to get what you need.

In production please restrict the records from the parcels table to only those that need processing - it will possibly get slow otherwise.

drazen · Sep 21, 2017

That is perfect.

Thanks

Calculating Postal costs on a report (1 Viewer)

drazen

Registered User.

Minty

AWF VIP

jdraw

Super Moderator

drazen

Registered User.

Minty

AWF VIP

drazen

Registered User.

Minty

AWF VIP

drazen

Registered User.

Attachments

Minty

AWF VIP

Attachments

drazen

Registered User.

Similar threads

Users who are viewing this thread