= in IIf function not working 1

[Lost500]

I am making a view to return the Sum of acreage records grouped by location. the view runs fine but i would like to use something like an IIf function to return the value "1" if the summation of acreage records is greater than 1. My code is as follows, sorry i forgot how to make code look like code in here:

SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres)/ dbo.tblTractInfo.TractGrossAcres AS Expr1

This works great i would like to make it:

IIf((SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres)/ dbo.tblTractInfo.TractGrossAcres) > 1, 1, SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres)/ dbo.tblTractInfo.TractGrossAcres) AS Expr1

when i do that i get the error:
'Error in list of function arguments: '>' not recognized.
Unable to parse query text.'

so i obviously don't understand how sql works there are greater than and equal to signs in the WHERE already and it works so... I don't understand how you can use the IIf function at all if you can use a >,< or =

this is really hanging me up I would greatly appreciate any help.

Thanks!
Lost

 

[jbenson001]

You will need to use a CASE statment. Look it up in BOL.

 

[markros]

Is it Access query?

Anyway, to use code here, use
[ code ] [ /code ] (no spaces)

Your IIF expressions should be

IIF(sum(myField)/myOtherField > 1,1,sum(myOtherField)/myYetAnotherField) as myNewField

In other words, you seem to added extra () and this confused the function.

 

[Lost500]

thanks markros,
it is a SQL view that i'm building on a SQL db in SQL server management studio.

I tried taking out the erroneous () but i am getting the same error message: 'Error in list of function arguments: '>' not recognized.
Unable to parse query text.'

my code is now as follows:

IIf(SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres)/ dbo.tblTractInfo.TractGrossAcres > 1, 1, SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres) / dbo.tblTractInfo.TractGrossAcres) AS Expr1

I'd like to give yall more to work with but i don't know where the problem is. let me know if providing any additional info will help you help me Thanks for the help already and i hope we can get this working!

 

[Lost500]

Thanks Jbenson,
can I use the Case statment inside of my Select statment? i'm confused on how to apply it.

Thanks for the help!

 

[markros]

Yes, since you used IIF I assumed it was Access. In SQL Server you use CASE, so

case when SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres)/ dbo.tblTractInfo.TractGrossAcres > 1 then 1 else SUM(dbo.tblTractLeaseAcres.ConfirmedNetAcres) / dbo.tblTractInfo.TractGrossAcres end AS Expr1

 

[Lost500]

sweet, yall rock. it seems to me that i am completley confused with my access and SQL server.

Can you also tell me what the sql server version of making the results from a query formatted as percent? in access its

FORMAT([FieldName], "Percent")

really thanks so much it really is great having this resource!

 

[markros]

In SQL Server there is no such function. You can convert your result to varchar(30) and add '%' at the end.

Otherwise move this to presentation logic, e.g. do it in the front-end. It is usually a good idea to move all extra formatting to the presentation tier and not to the data tier.

 

[Lost500]

Thanks markos you really helped