Query Error Function Isnt Avail .... Version: 97

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query Error Function Isnt Avail .... Version: 97

Author

Topic

edgare
Starting Member

1 Post

Posted - 2006-08-03 : 18:01:59

Hi

I have access 97 and here is the query i get error " Function isn't available in expressions in query expression
'Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='1')' Here is the full query why do i get this? How can i fix it? Thanks

SELECT Reason_Code.Reason_Description_Major, Reason_Code.Reason_Description_Sub, Count(Claim.Claim_Number) AS Total, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='1'),1,0)) AS Jan, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='2'),1,0)) AS Feb, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='3'),1,0)) AS Mar, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='4'),1,0)) AS Apr, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='5'),1,0)) AS May, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='6'),1,0)) AS Jun, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='7'),1,0)) AS Jul, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='8'),1,0)) AS Aug, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='9'),1,0)) AS Sep, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='10'),1,0)) AS Oct, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='11'),1,0)) AS Nov, Sum(IIf((Left([month_year],(InStr([month_year],'/'))-1)='12'),1,0)) AS Dec, Right([month_year],Len([month_year])-(InStr([month_year],'/'))) AS Year
FROM Source_Code INNER JOIN (Reason_Code INNER JOIN (Product_Code INNER JOIN Claim ON Product_Code.Product_Code = Claim.Product_Code) ON Reason_Code.Reason_Code = Claim.Reason_Code) ON Source_Code.Source_Code = Claim.Source_Code
GROUP BY Reason_Code.Reason_Description_Major, Reason_Code.Reason_Description_Sub, Right([month_year],Len([month_year])-(InStr([month_year],'/')))
HAVING (((Right([month_year],Len([month_year])-(InStr([month_year],'/'))))="2006"))
ORDER BY Count(Claim.Claim_Number) DESC;

THANKS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 14:20:20

I think it is the IIF function that needs to be installed on your system. this function is in a separate dll-file. I don't which right now.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources