My second is a Rounding Question

[JMLU]

I have a range of values that I'd like to round. For example for 1- 10, I
want to return 10, 11-20 = 20 then at 101-150=150, 151-200=200. Each record
is a single number. What would be the best expression to use?

 

[KARL DEWEY]

Create a table named Number_Range with three fields -
Num Low High
10 1 10
20 11 20
150 101 150
200 151 200

SELECT YourTable.*, Num
FROM YourTable, Number_Range
WHERE YourTable.YourNumberField Between Low AND High;

 

[JMLU]

Hi Karl,

I created the table but I'm beyond dense today.

SELECT YourTable.*, Num
FROM YourTable, Number_Range
WHERE YourTable.YourNumberField Between Low AND High;

Once I've created this table I use it to assign a number value but in what
field of the query? And what function? Please pardon my neophyte questions.

 

[KARL DEWEY]

Post your query SQL that you want to use this in and someone can edit it for
you. Open the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[JMLU]

Here's the code in my query - FinalQuantity is the field I want to round.

SELECT DISTINCT MyTable_Select.ID, MyTable_Select.[Ship Report Org Name],
Sales_Force_Account_Lead_Match.Match_1_company, MyTable_Select.[Ship State
Province], Sales_Force_Account_Lead_Match.Region,
Sales_Force_Account_Lead_Match.Match_1_ownerlastname, MyTable_Select.[System
Status], MyTable_Select.[Contract No], MyTable_Select.[Service Level],
MyTable_Select.[Contract End Date], MyTable_Select.[Product Id],
MyTable_Select.[Part Description], MyTable_Select.[Marketing Sub Category],
MyTable_Select.[Serial State], MyTable_Select.[Final Quantity],
MyTable_Select.[Serial No], MyTable_Select.[Elite Status]
FROM MyTable_Select LEFT JOIN Sales_Force_Account_Lead_Match ON
(MyTable_Select.[Ship Report Org Name] = Sales_Force_Account_Lead_Match.[Ship
Report Org Name]) AND (MyTable_Select.[Ship State Province] =
Sales_Force_Account_Lead_Match.[Ship State Province]);

Thank you for your assistance.

 

[JMLU]

Hi Ken,

The modules work but they reduce the number of total fields. I have a test
file that has 2850 records to 57. The Unique Records/Values are switched to
no. Why would that be happening?

Jim

Keven Denen recently posted a couple of useful functions for rounding up or
down to a multiple of a factor. His function for rounding up was as follows
(slightly amended to suit this scenario)

Public Function Ceiling(ByVal dNumber As Double, _
Optional ByVal dFactor As Double = 1) As Double

Ceiling = (Int(dNumber / dFactor) - _
(dNumber / dFactor - Int(dNumber / dFactor) > 0)) * dFactor

End Function

By calling this in another function you can cater for rounding to the nearest
10 or 50 by getting the integer of the base 10 logarithm of the number to
determine its size and therefore the factor to which it should be rounded:

Public Function GetRoundedVal(n As Double)

Dim intLog As Integer
Dim intFactor As Integer

intLog = Int(Log(n) / Log(10))

Select Case intLog
Case Is < 2
intFactor = 10
Case 2
intFactor = 50
Case 3
intFactor = 500
Case 4
intFactor = 5000
End Select

GetRoundedVal = Ceiling(n, intFactor)

End Function

In the above I've extended your pattern so that 1001 – 1500 would be rounded
to 1500, 1501-2000 to 2000, 10001 – 15000 to 15000, 15001-20000 to 20000 and
so on up to a maximum of 100000, but you can easily amend it if necessary.

You'd then simply call the second function to get the rounded value:

GetRoundedVal([The Number])

Ken Sheridan
Stafford, England

I have a range of values that I'd like to round. For example for 1- 10, I
want to return 10, 11-20 = 20 then at 101-150=150, 151-200=200. Each record
is a single number. What would be the best expression to use?