Help Needed - Formula for MS Access (1 Viewer)

Brent Janetzki · May 17, 2015

Hi I have spent several hours now staring at my screen trying to work out what the below converted excel to MS Access formula would be =IF(AND(N2<=J2,N2>K2),1,0).

I'm a Real Estate Agent and I am analysing the price range method sales method i.e where a property is listed for sale in a price range $300,000 to $350,000.
In a perfect world, I would like the formula to tell me in what quadrant of the price range did the property sell for under this method. For example if I list a property for sale between $300,000 and $350,000 I want the formula to tell me in which quadrant of the price range it ended up selling in which will either be Q1(75% of the price range or above), Q2 (50% to 75%), Q3 (25% to 50%) or Q4 (25% of below)of the price range it falls into. I.e If the property sold for $349,000 or above it would fall into Quadrant 1 with Q1 commencing at $337,500 ($300,000+75% of $50,000 being the difference between the upper and lower components of the price range).

If someone could help that would be greatly appreciated.

Thanks Brent

plog · May 17, 2015

I would create a function in a module for this. Here is that code:

Code:

Public Function get_SalesQuadrant(in_SalesAmount, in_LowerBound, in_UpperBound) As Integer
    ' determines what quadrant of sales range (in_LowerBound to in_UpperBound) sales (in_SalesAmount) falls in

Dim ret As Long
    ' return value of function
Dim int_Range As Long
    ' size of sales range
Dim int_SalesDif As Long
    ' total amount of sales over lower bound of sales range
Dim dbl_RangePercent As Double
    ' percentage of sales compared to range

ret = -1
    ' default error value -  sales under lower bound
    
int_Range = in_UpperBound - in_LowerBound
    ' calculates size of sales range
    
int_SalesDif = in_SalesAmount - in_LowerBound
   ' gets amount of sales over lower bound of range
   
dbl_RangePercent = int_SalesDif / int_Range
    ' calculates percentage of sales compared to range

If (dbl_RangePercent >= 0 And dbl_RangePercent < 25) Then ret = 4
If (dbl_RangePercent >= 25 And dbl_RangePercent < 50) Then ret = 3
If (dbl_RangePercent >= 50 And dbl_RangePercent < 75) Then ret = 2
If (dbl_RangePercent >= 75 And dbl_RangePercent <= 100) Then ret = 1
If (dbl_RangePercent > 100) Then ret = 0
    ' determines which quadrant sales falls into, if over 100% returns 0 as error

get_SalesQuadrant = ret

End Function

The only ambigous thing is what are the quadrant limits? What if the Sales fall exactly at 25, 50 or 75? Does it go into the lower or upper quadrant? In my code, it falls into the higher quadrant.

Brent Janetzki · May 17, 2015

Wow this was a quick response. I want the quadrant limits to be as follows:
Q1 should be 76% and above
Q2 should be 51% to 75%
Q3 should be 26% to 50%
Q4 should be 25% and below.

The next question is how do I create this as I am new to MS Access. What is a Function in a Module?

plog · May 17, 2015

A module is a container of all code you want available to your database. What you do is go to the ribbon, click on CREATE and in the area that appears is a section called Macros & Code. Click on the Module item in that grouping.

A new window will open, you paste my code in. Then tweak it to account for the specific limits you set and then you can use it in a query. In a query you would use it like so:

SalesQuadrant: get_SalesQuadrant(349000, 300000, 350000)

It will send for the calculation and return the correct answer. I hard coded your initial example data in there, it can very easily pull data from a query. Instead of the numbers, you would put the field names in their place:

SalesQuadrant: get_SalesQuadrant([SalePrice], [LowerLimit], [UpperLimit])

You could even mix and match, using a field in one spot and a hard coded number in the others.

Rx_ · Jul 7, 2015

Your user profile doesn't include location.
At the Denver Area Access Users Group (held at Microsoft) tonight's subject is Intro To VBA.
They also follow up with a hand-on lab at the Microsoft Store later in the month.
People can bring in real-world quesitons and get assistance.

pbaldy · Jul 7, 2015

FYI, thread moved out of the user groups forum.

CJ_London · Jul 7, 2015

not sure why you need a function, it can be calculated in a query

Quadrant

(SalePrice-LowerRange) \ ((UpperRange-LowerRange)/4))+1

although sales over the upper range will give you 5 or more and below the lower range will give you 0 or less but this can be fixed with a nested iif

Quadrant:iif(SalePrice<LowerRange,1,iif(SalePrice>UpperRange,4,((SalePrice-LowerRange) \ ((UpperRange-LowerRange)/4))+1))

plog · Jul 7, 2015

I wonder what the oldest post is I could reply to and rekindle a conversation on?

Think if I picked one from 2005 and gave some bad advice people would chime in?

Stay tuned.

Help Needed - Formula for MS Access (1 Viewer)

Brent Janetzki

New member

plog

Banishment Pending

Brent Janetzki

New member

plog

Banishment Pending

Rx_

Nothing In Moderation

pbaldy

Wino Moderator

CJ_London

Super Moderator

plog

Banishment Pending

Similar threads

Users who are viewing this thread