IIF Returns Text Values? 1

[khwaja]

I am a beginner and this may be a dumb question for many. In a query, I am trying to use IIF function to apply few conditions which when true should return a number. However, I have noticed that returned values are always in text and not number which necessitate the need for converting the output. As an example, the formula I am using is as under:

FSScore: IIf(IsNull([DevType]),0,IIf([DevType]="New" And [Loc Type]="SU FS" And [Type]="Supermarket",10,"&quot

I am asking that 10 should be returned when all the conditions are met.
Regards

 

[tlbroadbent]

You told IIf to return a string rather than a number. The "" value at the end of the function is a string. Access must return the same datatype for the column in all rows of the result set. If the function returns "" in one row, Access will return that column as a string because it can't return "" as a number. Change the "" to Null or 0 (zero) to return a numeric value in the column. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

I cann't thank you enough for helping me with this. Would the solution be different in case when I am calculating difference of dates and then substracting some days to calculate lease expiry date. Should I be still using Null or 0 to return date format?

 

[tlbroadbent]

I'm unclear of your meaning. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Sorry for not being too clear. I have had a similar problem with another database I am working which basically monitors when the proprty leases expire or notices fall due. I calclulated the total lease term in years based on following:

Lease Term: IIf(IsNull([LeaseTermFrom]),Null,CInt(([LeaseTermTo]-[LeaseTermFrom])/365.4))

Lease Term From and To are date fields. Despite replacing double quotes with Null (as you advised), I still get numbers in text format.

What could be the possible mistake in my approach?

Regards

 

[tlbroadbent]

I tested your statement in Access 2000 and created a numeric column so I'm not sure why you are getting a text column. I recommend a slight modification. Move the CInt inside one parentheses.

Lease Term: IIf(IsNull([LeaseTermFrom]),Null,(CInt([LeaseTermTo]-[LeaseTermFrom])/365.4))


Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.