Converting text field to number without #Error in empty fields

[kharder]

I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.

I have tried to convert the fields with Cint, but I get an #Error in the empty fields.

Is there any way to solve this??

Thanks in advance..

 

[kharder]

I solved the problem with three IIf sentenses..
y: IIf(IsNull([x]);0;[x])
z: IIf([y]="0";"*";[y])
result: IIf([z]="*";"*";FormatNumber([z];0))

Is there any way to create one sentense of this??

 

[EMP]

I solved the problem with three IIf sentenses..
y: IIf(IsNull([x]);0;[x])
z: IIf([y]="0";"*";[y])
result: IIf([z]="*";"*";FormatNumber([z];0))

I doubt if result is a numeric column as * is text, not a number.

^

 

[kharder]

My solution works in 2003 but does anybody know how to get it to work in 97?

 

[john471]

FormatNumber is not an AC97 Function, hence why it won't work in 97.

Would this work:-
Format(nz([X],"*"),"#")

 

[kharder]

Thank you.

That does the job with the symbol "*". But I want the numbers stored to be shown, not like "1025" but like "1.025". Is that possibel in Ac97??

 

[john471]

Format(nz([X],"*"),"#.#")