formatting question 2

[istone]

Hi all,
I have this formula:
IIf([BP BILLED TO]="N/A",[Assests]=Null,Format([Assests],"Currency"))

it gives me this result

Assets
$1,544,455.69
$2,587,001.12

I would like to show the numbers without the cents like this:

Assets
$1,544,455
$2,587,001

I tried properties but not available

thanks in advance

 

[dhookom]

If you are using this in a control source then you should use the format property. However your expression is in error.

This [Assests]=Null should probably just be Null.

Try an expression like:
IIf([BP BILLED TO]="N/A",Null,Format([Assests],"$0"))

Again, I would try set the format property of the control and use a control source of only:
IIf([BP BILLED TO]="N/A",Null,[Assests])



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

thanks for help.

This query is being exported to an excel file which is used to merge with word.

this formula: IIf([BP BILLED TO]="N/A",Null,Format([Assests],"$0"))gives me this result: $13789104 which round the last 2 numbers. I am looking to format this way 13,789,104

Your other suggestions IIf([BP BILLED TO]="N/A",Null,[Assests]) is correct except when I export it to excel I can't see the right formatting on the Formula bar. When we merge it with words the formatting is not showing the $ sign.

that's why i did this formula: IIf([BP BILLED TO]="N/A",[Assests]=Null,Format([Assests],"Currency"))
it works except it shows the cents.

 

[istone]

Problem solved. I did the following:
IIf([BP BILLED TO]="N/A",Null,Format([Assests],"$#,#00"))

thanks again for your help

 

[istone]

ok this one works:

IIf([BP BILLED TO]="N/A",Null,Format([Assests],"$#,#00"))
but it rounds the numbers to the highest...

$1,245,546,89 Becomes $1,245,547

I am looking to show this number: $1,245,546 i just want to hide the last 2 digits

thanks

 

[dhookom]

I missed all that information on being exported to Excel for a Word merge in your initial posting ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

How about:

[tt]IIf([BP BILLED TO]="N/A",Null,Format(Int([Assests]),"$#,#00"))[/tt]

 

[istone]

Remou, thank you so much. It worked.