Nz Returning text instead of number

[shift2076]

hi all,

here's the dealio... i need perform an aggregate funtion off of a certain field, but it contains null values. i tried using Nz and an Iif IS NULL statement to get rid of the nulls. but both methods return the values as text. obviously, i can't get an Avg of these text values.

here's the two methods i tried:

Format(IIf([AvailTab].[Per00] Is Null,"1",[AvailTab].[Per00]),"Percent") AS Per01Test

Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00

how can i return the data as a number?

thanks,
mj

 

[gemma-the-husky]

i had that, i think theres a long thread on it

upshot was if the typecast isnt automatic you may need a clng(text) or cdbl(text) etc to force it to the correct format.

 

[boblarson]

Get rid of the quotation marks around the number:

Format(Nz([AvailTab].[Per00],"1"),"Percent") AS Per00

Format(Nz([AvailTab].[Per00],1),"Percent") AS Per00

 

[shift2076]

Solution!

thank you bob, you sent me down the right track. the Nz still returned it as text, even without the quotes, but the other formula worked:

IIf([AvailTab].[Per00] Is Null,1,[AvailTab].[Per00])AS Per00Test

i also had to get rid of the format statement in the sql, and just formatted it in design view.

peace!
mj

 

[Brianwarnock]

i also had to get rid of the format statement in the sql, and just formatted it in design view.

peace!
mj

If you do that the Nz works also.

Brian