Round to nearest 200 in a query

[bruce24444]

I'm designing a database which takes into consideration the total square footage of a house and multiplies it by a predetermined multiplier to determine the cost. However, the table with the predetermined multipliers is designed for every 200 sq. ft.

IE:
Total Sq. foot - - - - Multiplier
400 - - - - - - - - - - - 1.032
600 - - - - - - - - - - - 1.025
800 - - - - - - - - - - - 1.013
1000 - - - - - - - - - - - 1.000
1200 - - - - - - - - - - - 0.992
etc, etc to 6000 sq. ft

I have another query which takes into consideration the info provided by the user and returns a sq. footage. but this number may be 1120.

What I'm trying to do is take this "1120" and round it up to 1200, so a value for the multiplier would correspond.

So far this is the best function I've been able to get return some value, however it's retuning really strange numbers like and every time I go from design mode to datasheet view, I get a different result.

SELECT [que:Main].Shape, [que:Main].[Total Sq Footage], Rnd([que:Main]![Total Sq Footage]) AS [Sq Foot]

[Scott Price]

You're going to need a coded function something like the following in order to accomplish this. Being a public function you can use it inside a query... This is quick and dirty...

Paste this into a standard code module, if you need instructions how to do so, let me know.

[CODE=vb]Option Explicit

Public Function Round200(ByVal sqft As Integer) As Integer

Dim squareFootage As Integer

squareFootage = 0

Select Case sqft
Case Is <= 400
squareFootage = 400
Case 401 To 600
squareFootage = 600
Case 601 To 800
squareFootage = 800
Case 801 To 1000
squareFootage = 1000
Case 1001 To 1200
squareFootage = 1200
'etc etc etc
End Select

Round200 = squareFootage

End Function[/CODE]

The sql to call this function will look like this:

Select Round200([SQFTFieldName]) As SqFootage

Regards,
Scott

[bruce24444]

Thanks for the quick response. I’m fairly new to Access and have never used modules before, but I have a fair idea of what you’re trying to explain.

So what I did is copied your coding into a module and called it Round200.

Option Compare Database
Option Explicit
 
Public Function Round200(ByVal sqft As Integer) As Integer
 
Dim squareFootage As Integer
 
squareFootage = 0
 
Select Case sqft
    Case Is <= 400
        squareFootage = 400
    Case 401 To 600
        squareFootage = 600
    Case 601 To 800
        squareFootage = 800
    Case 801 To 1000
        squareFootage = 1000
    Case 1001 To 1200
        squareFootage = 1200
    'etc etc etc
End Select
 
Round200 = squareFootage
        
End Function

Then went back to query , into SQL view and copied your example but changed it so the ([SQFTFieldName]) was where the module gets the info from and it looks like this:

Select Round200([que:Main].[Total Sq Footage]) As SqFootage

But when I go to view I get:

The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect.

OR

Do I somehow integrate the coding you’ve provided into the query I have already y started

SELECT [que:Main].Shape, [que:Main].[Total Sq Footage], [que:Main]![Total Sq Footage] AS [Sq Foot]
FROM [que:Main], [tbl:ShapeMultiplier]
GROUP BY [que:Main].Shape, [que:Main].[Total Sq Footage];

Thanks in advance for any assistance you can provide.
Bruce

[missinglinq]

Or maybe a simpler formula (as much as I dislike iif())

SqFt = iif (SqFt Mod 200 <> 0, SqFt + 200 - (SqFt Mod 200), SqFt)

Linq ;0)>

[ADezii]

I'm designing a database which takes into consideration the total square footage of a house and multiplies it by a predetermined multiplier to determine the cost. However, the table with the predetermined multipliers is designed for every 200 sq. ft.

IE:
Total Sq. foot - - - - Multiplier
400 - - - - - - - - - - - 1.032
600 - - - - - - - - - - - 1.025
800 - - - - - - - - - - - 1.013
1000 - - - - - - - - - - - 1.000
1200 - - - - - - - - - - - 0.992
etc, etc to 6000 sq. ft

I have another query which takes into consideration the info provided by the user and returns a sq. footage. but this number may be 1120.

What I'm trying to do is take this "1120" and round it up to 1200, so a value for the multiplier would correspond.

So far this is the best function I've been able to get return some value, however it's retuning really strange numbers like and every time I go from design mode to datasheet view, I get a different result.

SELECT [que:Main].Shape, [que:Main].[Total Sq Footage], Rnd([que:Main]![Total Sq Footage]) AS [Sq Foot]

[CODE=vb]
IIf(Total_Squar e_Footage < 400, 400, IIf(Total_Squar e_Footage Mod 200 = 0, Total_Square_Fo otage, (Fix(Total_Squa re_Footage / 200) * 200) + 200))
[/CODE]

[bruce24444]

Thanks everyone for your suggestios, it returns exactly what I was hoping for

Bruce

[Scott Price]

Just as a curiosity I plugged this in and this is working just fine for me in my test database:
[CODE=sql]
SELECT Round200( PRODUCT.blahbla blah) As Rounded
FROM PRODUCT;[/CODE]

Rather strange that it's not working for you! What version of Access are you using?

Really this is kind of beside the point since you have a workable solution, it's just kind of strange :-)

Regards,
Scott

[bruce24444]

Right now we're still using Access 97 at work and I know some suggestions only work with 2003 and newer. But I will try your suggestion again, just to see what happens.

Once again thanks for your help.

[Scott Price]

That's probably the reason. I'm not sure on the A97 versus A2003 specifics of this code, but I'll assume that's the issue! Good to have that solved at least :-)

Regards,
Scott

[missinglinq]

The solutions here involving the iif() function won't work in ACC97, of course. It didn't arrive until 2000. You could write a custom function using the If...End If construct to do the same thing and then call that fumction from the query.

Linq ;0)>