Wrox Programmer Forums Calculating a field based on a lookup range
 | Search | Today's Posts | Mark Forums Read
 Access VBA Discuss using VBA for Access programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

April 23rd, 2004, 12:01 PM
 Registered User Join Date: Apr 2004 Location: , , . Posts: 1 Thanks: 0 Thanked 0 Times in 0 Posts
Calculating a field based on a lookup range

(As a beginner) I am trying to do 2 things

1) On a form, calculate a field (sales total) based on a lookup range – i.e. form has a sales price (data entry) and a total (calculated control that refers to a table - tblRate)
The tblRate has 4 fields - LowPrice, HighPrice, SalesRate, CommRate
The first 2 records are \$0, \$4.99, 30%, 40% and \$5.00, \$6.99, 40%, 50%
The idea being that if the sales price is \$3 look it up in the tblRate (in this case - between \$0 and \$4.99 and multiply the sales price by 30% etc.)

Given that there may be many records in the tableRate, how do I code the calculation on the form without using a lot of code and with the ability to add more records easily in the tblRate.

2) Once the form is calculated – what’s the easiest way to write the major form fields to a new table (I know you don’t usually store calculated fields- but the rate changes frequently so I need to have a history transaction file) – how do you write the code to store the ‘output’ of the calc fields

April 29th, 2004, 06:41 PM
 Authorized User Join Date: Oct 2003 Location: Cleveland, OH, USA. Posts: 75 Thanks: 0 Thanked 0 Times in 0 Posts

For the first thing, there's a couple of things that you can do. I am assuming a simple screen example where you have textboxes named SalesPrice, SalesRate, CommRate, SalesCalcComm, and CommCalcComm, as well as a button to retrieve the values called cmdRetrieveSalesCommRates for simplicity.

The first is to use a domain function named DLookup() which would work something like this:

Private Sub cmdRetrieveSalesCommRates_Click()

If Not IsNumeric(Me.txtSalesPrice) Then

Me.txtSalesRate = DLookup("SalesRate", "tblRate", "LowPrice <= " & Me.txtSalesPrice & " AND HighPrice >= " & Me.txtSalesPrice)

Me.txtSalesCalcComm = Me.txtSalesPrice * Me.txtSalesRate * .01

Me.txtCommRate = DLookup("CommRate", "tblRate", "LowPrice <= " & Me.txtSalesPrice & " AND HighPrice >= " & Me.txtSalesPrice)

Me.txtCommCalcComm = Me.txtSalesPrice * Me.txtCommRate * .01

Else

MsgBox "Sale Price must be a numeric value"

End If

End Sub

You can use the DLookup() function for any field used like this on the screen. DLookup() has 3 arguments - the first is the name of the column that you want to retrieve data from, the second is the name of the table, and the third is the WHERE condition. In the example, you can see where I use string concatenation to build the WHERE condition using a mixture of literal values (LowPrice <=, etc) combined with the values retrieved from the screen. Keep in mind that you may want to first check that the txtSalesPrice field on the screen does not contain a numeric value before running the code.

The second way you can do this is to use ADO code, which could be more helpful in a situation where you need to retrieve multiple column values from a table - you can do something like this:

Private Sub cmdRetrieveSalesCommRates_Click()

Dim strSQL As String

strSQL = "SELECT * FROM tblRate " & _
"WHERE tblRate.LowPrice <= " & Me.txtSalesPrice & _
" AND tblRate.HighPrice >= " & Me.txtSalesPrice

Set cnn = CurrentProject.Connection

If rst.BOF And rst.EOF Then
MsgBox "Cannot locate rates for specified sales price"
rst.Close
cnn.Close
Exit Sub
End If

Me.txtSalesRate = rst!SalesRate
Me.txtSalesCalcComm = Me.txtSalesPrice * Me.txtSalesRate * .01
Me.txtCommRate = rst!CommRate
Me.txtCommCalcComm = Me.txtSalesPrice * Me.txtCommRate * .01

rst.Close
cnn.Close

End Sub

Either of these examples would work - it just depends on your preference. If you do like DLookup(), you may want to look into the DCount() and DSum() functions as well which are both available in VBA.

As far as the history transaction file goes, I am assuming that you want to create a posting of each sale. I would highly recommend that along with other pertinent info that you need, that you store the date/time, price, and the rates in the transaction file. You can always recalculate the sale and commission amounts using the stored rates at that time. Grant you, your point on not storing calculated fields is a good one - in the case of keeping a history transaction log though, keeping a "snapshot" of the rates at that moment in time of the sale could actually be beneficial.

Hope that helps.

Warren :D

 Similar Threads Thread Thread Starter Forum Replies Last Post Lookup Field for Report eusanpe Access 2 March 9th, 2007 08:38 AM Calculating and filtering age based on date shawnm Classic ASP Databases 4 November 12th, 2005 06:08 AM Create lookup based on field value s.hendy Access 2 May 20th, 2005 04:08 AM Update city field based on zip field nganb SQL Server ASP 0 April 22nd, 2004 10:30 PM lookup table current field. Squid Access 0 December 21st, 2003 07:08 PM