Formatting issue 1

[istone]

Hi,
I have this query that I export to an excel sheet. Everything works fine except 3 columns's format is not right.
in the query they look like this:
Column1 Colum2 Column3
$45.24 $779.93 $1,143.48

when I click on the number $45.24 it shows 45.23871, $779.93 shows $779.934 and $1,143.48 shows $1,143.4844

I went to properties and change Format to Currency and Dicimal Places to 2 but still not formatted right.

I export the query and in the excelsheet the columns (cells) look fine but not the formula bar.

I use the excelsheet to merge and when I do the merge The numbers are formatted wrong.
Thanks in advance

 

[lespaul]

what's the SQL of the query?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[istone]

SELECT QueryFinal.PlanID, QueryFinal.PLAN, QueryFinal.MaxOfBP_Fee, QueryFinal.[MaxOfMaxOfTotal Fee Amount], IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE" And [Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0),IIf([Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),[MaxOfMaxOfTotal Fee Amount]))) AS [Total Amount]
FROM QueryFinal
GROUP BY QueryFinal.PlanID, QueryFinal.PLAN, QueryFinal.MaxOfBP_Fee, QueryFinal.[MaxOfMaxOfTotal Fee Amount], IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE" And [Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0),IIf([Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),[MaxOfMaxOfTotal Fee Amount])));

I only include the columns with the issue: MaxOfBP_Fee, MaxOfMaxOfTotal Fee Amount and Total Amount
columns plan Id and plan are fine.
Thanks

 

[lespaul]

maybe the Format function? I didn't go through the huge IIF statement to do that one, you may want to do the formatting in the QueryFinal query instead of here:

SELECT PlanID, PLAN, Format(MaxOfBP_Fee, #.##), Format([MaxOfMaxOfTotal Fee Amount], #.##), IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE" And [Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0),IIf([Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),[MaxOfMaxOfTotal Fee Amount]))) AS [Total Amount]
FROM QueryFinal
GROUP BY QueryFinal.PlanID, QueryFinal.PLAN, QueryFinal.MaxOfBP_Fee, QueryFinal.[MaxOfMaxOfTotal Fee Amount], IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE" And [Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),IIf([BP BILLED TO]="PLAN ASSETS" And [QRT BILLED TO]="INVOICE",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([MaxOfQRTRLY FEE],0),IIf([Morningstar Sentence after Total]="Morningstar Advice Online from Plan Assets",nz([MaxOfMaxOfTotal Fee Amount],0)-nz([Momingstar Clearfuture Per Par Fee$100/part/month],0),[MaxOfMaxOfTotal Fee Amount])));

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[istone]

Sorry to reply late. I just got to your answer. I did it but it does not seem to work for me.

to keep it simple I tried it on this code but :

SELECT QueryFinal.PlanID, Format(QueryFinal.MaxOfBP_Fee, #.##), Format(QueryFinal.[MaxOfMaxOfTotal Fee Amount],#.##)
FROM QueryFinal
GROUP BY QueryFinal.PlanID, QueryFinal.MaxOfBP_Fee, QueryFinal.[MaxOfMaxOfTotal Fee Amount];

 

[istone]

Problem solved. I did the following and it worked:

Format(QueryFinal.MaxOfBP_Fee,"Standard")

Thanks for your help Leslie