Currency/text display issues

[Akazuba]

I have a database and report of employee total compensation. (The "report" is an Access form, actually. There is one page for each record/employee.) It lists all the benefits offered by our organization, with the annual amount the organization pays vs. what the employee pays. If the employee does not opt for a certain benefit, I wanted the form to display "NONE" rather than just $0.00, because there are some benefits an employee is covered by but he/she pays nothing. So, since I knew I couldn't have text and numbers in the same column, I converted each whole column to text in Excel, with $ signs and commas, so that my currency figures would display correctly and I could keep my "NONE" values. (If I opted instead just to change the column format to text within Access, the currency figures don't display correctly.)

The form works fine how it is now, but I want to make it as easy as possible to duplicate this with new figures, and I feel that what I did with using the TEXT() function in Excel perhaps would be difficult for some people who may be producing this print-out in the future.



I understand that I cannot store text and numbers in the same column... but is there any workaround I could use just for my form? I tried making a query that had an IIF statement using the CCur() function, but it gives me #Error when I ask it to display text for the else portion.

Any ideas??

[orange]

I think you'd be better off using numeric data and the Format(yourNumber,"Currency").
It would be consistent.

see http://www.techonthenet.com/access/f...ing/format.php for other Formats

[Akazuba]

Thanks, orange. I am a newbie to Access, so I didn't know about the Format() function. So I guess it works differently than the CCur() I was trying to use? I replaced where I had CCur() with your Format() instead in an IIF statement for my query like this:

Expr1: IIf([MedRx] Is Null,"NONE",Format([MedRx],"Currency"))

And now finally my form displays currency or "NONE" in the same field! Hooray!!

[orange]

Glad you got it solved.