Instead of a hard to maintain nested IIf statement, consider using a Case
statement. Paste the following in a module and compile it.
Function fGroups(strGroups As Variant) As Long
Dim TheGroups As Long
Select Case strGroups
Case 35 To 44.99
TheGroups = 1
Case 45 To 54.99
TheGroups = 2
<<snipp>>
Instead of a VBA procedure, because this is after all the 'Queries'
group, how about using Jet SQL e.g.
SWITCH(
ave1 BETWEEN CCUR(35.0000) AND CCUR(44.9999), 'Group 1',
ave1 BETWEEN CCUR(45.0000) AND CCUR(54.9999), 'Group 2',
ave1 BETWEEN CCUR(55.0000) AND CCUR(64.9999), 'Group 3',
ave1 BETWEEN CCUR(65.0000) AND CCUR(74.9999), 'Group 4',
ave1 > CCUR(75.0000), 'Group 5',
TRUE, '{{NO GROUP}}'
) AS group_name
But because this is a group about SQL DBMS, how about using *data*
rather than hard-coding the logic into the front end or the schema
(hint: inflation may cause the groups' limits to change):
CREATE TABLE Ave1Groups (
group_name VARCHAR(10) NOT NULL PRIMARY KEY,
min_value CURRENCY
DEFAULT -922337203685477.5808 NOT NULL
UNIQUE,
max_value CURRENCY
DEFAULT 922337203685477.5807 NOT NULL
UNIQUE
)
;
You'll probably want some more constraints to ensure min_value <=
max_value and unambiguous value ranges: no overlaps, no gaps, etc.
Those DEFAULT values may look ridiculously extreme but that's what you
get when you choose CURRENCY ;-)
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 1', CCUR(35.0000), CCUR(44.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 2', CCUR(45.0000), CCUR(54.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 3', CCUR(55.0000), CCUR(64.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value, max_value) VALUES (
'Group 4', CCUR(65.0000), CCUR(74.9999))
;
INSERT INTO Ave1Groups
(group_name, min_value) VALUES (
'Group 5', CCUR(75.0000))
;
To test the table:
CREATE TABLE Test (ave1 CURRENCY)
;
INSERT INTO Test (ave1) VALUES (50)
;
INSERT INTO Test (ave1) VALUES (0.01)
;
INSERT INTO Test (ave1) VALUES (123456789.1234)
;
Finally, the query:
SELECT T2.ave1,
IIF(DT1.group_name IS NULL, '{{NO GROUP}}', DT1.group_name) AS
group_name
FROM Test AS T2 LEFT JOIN
(
SELECT T1.ave1, G1.group_name
FROM Test AS T1, Ave1Groups AS G1
WHERE T1.ave1 BETWEEN G1.min_value AND G1.max_value
) AS DT1 ON T2.ave1 = DT1.ave1;
The idea is to allow the groupings' value ranges to change without
having to re-write your schema DDL, DML, front end code, etc. Put
another way, you change the data (easy, low risk, customer's side,
etc) rather than change the schema (difficult, high risk, coder's
domain, etc).
Jamie.
--
