Calculated Field in Query to Produce a Range (1 Viewer)

ALewis06

Registered User.
Local time
Today, 01:28
Joined
Jun 21, 2012
Messages
124
Some of the fields in my table are:
ID
AccountName
AccountID
Material
Revenue
Sold Date

I'd like to create a query with a calulated field. The field would be called Revenue Range. I want the Revenue Range results to be as follows:

Revenue Revenue Range
<=$1000 1K or Less
<=$100000 1-100K
<=$250000 100-250K
>=$250000 Over 250K

My attempt rendered an error. What is wrong with expression I wrote?

Revenue Range: IIf([tblAccounts]![Revenue]<=1000,"1K or Less",([tblAccounts]![Revenue]<=100000,"1-100K",([tblAccounts]![Revenue]<=250000,"100-250K",([tblAccounts]![Revenue]<=>=250000,"Over 250K",""))))
 

plog

Banishment Pending
Local time
Today, 00:28
Joined
May 11, 2011
Messages
11,613
You've nested Iif statements, without using the 'Iif' part. This:

IIf([tblAccounts]![Revenue]<=1000,"1K or Less",([tblAccou

Should be this:

IIf([tblAccounts]![Revenue]<=1000,"1K or Less",Iif([tblAccou

That was your error. However, when you get that nested with functions, its time to think of a different way. I would suggest either a custom VBA function that you pass the Revenue value to and then that function returns the appropriate Range, or create a table with the low end of the range, the high end of the range and the range text, then when you need the range you do a Dlookup into that table using the Revenue value.
 

ALewis06

Registered User.
Local time
Today, 01:28
Joined
Jun 21, 2012
Messages
124
I'm too new to it to even begin thinking about custom VBA functions but thanks for your help.
 

Users who are viewing this thread

Top Bottom