How to force 2 placeholder in sum column? 3

[monkeysee]

I have a table with a field named Dollars and another named Cents. These are text fields. The reason for the separate fields is that these will print out on a preprinted insurance claim form that does not support the normal $&cents. In my cents properties in the table I have an input set up to show 2 places, ie: 00, with a default of 00.

Secondly I have a query set up to total both these columns. However, if, all the cents in the individual claim record add up to 0, the output is 0, I need it to show the double 00. I have to have the 2 placeholders in order for the claim to be read properly by the provider's scanner.

How can I force the 0 to become 00 in the query so that it will show up correctly on the report when printing out on the preprinted form?

 

[Remou]

Format should suit:

Format(Cents,"00")

 

[monkeysee]

sounds good, where would I put this? In the table itself or in the query somewhere?

 

[Remou]

In the query would be best. You would use format on the field line, so the SQL reads:

Select Format([Cents],"00") As FC, OtherField ...

 

[monkeysee]

Excellent! Works! Thank you a bunch!

 

[monkeysee]

Well, I thought it would work, but when I put it in with the "sum cents column" I got an error. This is my original sql without the change. How would I insert your 'fix' in this?

SELECT Service.VisitID, Sum(Procedure.DollarAmt) AS SumOfDollarAmt, Sum(Procedure.Cents) AS SumOfCents, Count(Service.DOS) AS CountOfDOS
FROM [Procedure] INNER JOIN Service ON Procedure.ProcedureID = Service.ProcedureID
GROUP BY Service.VisitID;

Thanks again for your help!

 

[PHV]

Replace this:
Sum(Procedure.Cents) AS SumOfCents
with this:
Format(Sum(Procedure.Cents), "00") AS SumOfCents

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

so that it will show up correctly on the report

I would not do any formatting in the query. Set the format property of the text box in the report.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Furthermore, what should happen if Sum(Procedure.Cents) is greater than 99 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Good catch PH.

The reason for the separate fields is that these will print out on a preprinted insurance claim form that does not support the normal $&cents.

I think this is a mistake by allowing the desired display output to drive your table structure. I would store the dollars and cents in a single field an use an expression like:
[tt][blue][Amount] Mod 100[/blue][/tt]
or
[tt][blue]Sum([Amount]) Mod 100[/blue][/tt]
to show the cents.

Duane
Hook'D on Access
MS Access MVP

 

[monkeysee]

PHV - Thanks! It works!

dhookom, I tried formatting the text box on the report form, but it didn't work. strange but don't know why.

PHV - At most, with my scenario, the total in cents column would be .32 in this situation. But you do bring up a good tho't for a future problem.

dhookom, to remedy this with your solution, where would I put your expression?

thank you both for your help!

 

[dhookom]

Ooops, my bad on the expression.

If you had a field containing both the dollars and cents, you could add a text box to the detail section of your report like:

To display only the cents value:
=[Amount]-Int([Amount])

To display only the dollar value:
=Int([Amount])

To display the total dollars:
=Int(Sum([Amount]))

To display remainder cents:
=Sum([Amount])-Int(Sum([Amount]))


Duane
Hook'D on Access
MS Access MVP