Access 2003 iif function

Can someone tell me how i can create an iif function to do the following?
I need to look a field that has an amount in it. if it is 90K or less make i
t
1 if its 180 to 90001 make it 2 etc...
So for every 90K in that amount add 1..
I tried to do a VB code to create a ceiling funciton but Access keeps giving
me an error saying that my function is undefinded? Had a friend run my code
and it worked for him. I did update to the most current service pack and it
still errors out...
Can anyone tell me how I can make either the function work or how to do an
iif function to make it work?
Thank you so much for any help you can give me> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
SELECT val = CASE WHEN column < 90000 THEN 1
WHEN column < 180000 THEN 2
WHEN column < 270000 THEN 3
ELSE 4 END
FROM table
This is covered here (along with some other things that will help an Access
person moving to SQL Server):
http://www.aspfaq.com/2214|||Please repost this to a Access NG.
HTH
Jerry
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:566081edf8196@.uwe...
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
> it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps
> giving
> me an error saying that my function is undefinded? Had a friend run my
> code
> and it worked for him. I did update to the most current service pack and
> it
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||ummmm... divide by 90k and add 1? Assuming integers, then:
select 89000 / 90000 + 1 -- = 1
select 90001 / 90000 + 1 -- = 2
select 810001 / 90000 + 1 -- = 10
Payson
magikgb via webservertalk.com wrote:
> Can someone tell me how i can create an iif function to do the following?
> I need to look a field that has an amount in it. if it is 90K or less make
it
> 1 if its 180 to 90001 make it 2 etc...
> So for every 90K in that amount add 1..
> I tried to do a VB code to create a ceiling funciton but Access keeps givi
ng
> me an error saying that my function is undefinded? Had a friend run my cod
e
> and it worked for him. I did update to the most current service pack and i
t
> still errors out...
> Can anyone tell me how I can make either the function work or how to do an
> iif function to make it work?
> Thank you so much for any help you can give me|||Thank you Aaron... I am new to VB is this VB code or just straight SQL?
Aaron Bertrand [SQL Server MVP] wrote:
>SELECT val = CASE WHEN column < 90000 THEN 1
> WHEN column < 180000 THEN 2
> WHEN column < 270000 THEN 3
> ELSE 4 END
>FROM table
>This is covered here (along with some other things that will help an Access
>person moving to SQL Server):
>http://www.aspfaq.com/2214|||This is T-SQL. You are posting to a SQL Server newsgroup. If you are
trying to write VB code or Access SQL, please post to a more appropriate
newsgroup. Then people won't waste their time providing you with useless
solutions...
"magikgb via webservertalk.com" <u15188@.uwe> wrote in message
news:5660b960e4dd5@.uwe...
> Thank you Aaron... I am new to VB is this VB code or just straight SQL?
> Aaron Bertrand [SQL Server MVP] wrote:|||Aaron.. Sorry new to this too...
Aaron Bertrand [SQL Server MVP] wrote:
>This is T-SQL. You are posting to a SQL Server newsgroup. If you are
>trying to write VB code or Access SQL, please post to a more appropriate
>newsgroup. Then people won't waste their time providing you with useless
>solutions...
>
>[quoted text clipped - 16 lines]

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!
You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>

About Count(Distinct) in AS

Hi,
Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
"Sum(IIF())" Syntax?
If it could be, how could I open or write Count(Distinct) or Sum(IIF())
function or syntax in Cube?
As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
Thanks!You can add an MDX expression in the cube definition, then the results of
the expression will be available as a cube cell, without re building the
cube...THis can be done in the cube editor of AS.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:uuY2g6nfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Could Cube or MDX in Analysis Services execute "Count(Distinct)" or
> "Sum(IIF())" Syntax?
> If it could be, how could I open or write Count(Distinct) or Sum(IIF())
> function or syntax in Cube?
> As we know, MDX could execute Sum(IIF()), and how about Count(Distinct)?
> Thanks!
>