Hello all. I hope you are having a great weekend.

I am running into a problem that basically results in eye candy (nice but not totally necessary) on a report.

As of now, my Starting, Ending and Total Mileages do not have a comma separator. I would like to place a comma in the proper place in these fields.

The only things I can find online that pertains to this do not work for me no matter what I try (and I've spent hours trying). Here is what I have referenced...

Filemaker Help; Formatting Numbers in a Text Calculation Field and

Filemaker Help; How to Handle Formatting Number, Date and Time Merge Fields and Formatted Numbers Within Text Fields.

Here is the code I am using now...

If ( IsEmpty ( Mileage_Starting ); ""; "Starting Mileage: " & Mileage_Starting) & ¶ &

If ( IsEmpty ( Mileage_Ending ); ""; "Ending Mileage: " & Mileage_Ending) & ¶ &

If ( IsEmpty ( Mileage_Total ); ""; "Total Miles: " & Mileage_Total) & ¶ &

"Amount Due: $" & EarnedToday

Please bear in mind that the mileage figures are not always 6 digits in length. The could run anywhere from 1 mile to 1,000,001+ miles.

Also, the .00 on the back end of the Amount Due is not necessary. Most companies use rounded numbers.

Thanks in advance for any help with this matter.

Regards,

Charlie

How come you are using text?

Because if there are no mileage figures, say the driver didn't drive that day, then I don't want the field labels appearing on the report as below. If I save the merge field as it is written and as a number, it returns only numbers formatted as below.

Try =

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) & ¶ ) &

... etc.

That worked beautifully. Thank you!

Hey Comment, Here's one for ya'...

Using the NumToJText function worked perfectly but I'd like to go a little further with the eye candy aspect if you (or anyone else) can help.

The problem; the field isn't shrinking on the printout and from what I can gather here, it has something to do with the JText function and using the TrimAll function as a fix.

I know the problem is within this field because when I removed it, everything else slid and shrunk perfectly.

Needless to say, the information contained in the link above is completely Greek Japanese to me. A sample of the printed field is attached.

As we would say down south, Thanky.

Can you post the current version of your calculation?

If (not IsEmpty ( Mileage_Starting); "Starting Mileage: " & NumToJText ( Mileage_Starting;1;0 );"") & ¶ &

If (not IsEmpty ( Mileage_Ending); "Ending Mileage: " & NumToJText ( Mileage_Ending;1;0 );"") & ¶ &

If (not IsEmpty ( Mileage_Total ); "Total Miles: " & NumToJText ( Mileage_Total; 1; 0);"") & ¶ &

If (not IsEmpty ( EarnedToday );"Total Due: $" & NumToJText ( EarnedToday; 1; 0))

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) & ¶ ) &

If (not IsEmpty ( Mileage_Starting); "Starting Mileage: " & NumToJText ( Mileage_Starting;1;0 );"") & ¶ &

Can you spot the difference?

Yes, and thank you. I wasn't aware that you could close the parentheses outside the carriage return even though that is what you wrote in your earlier post. I guess you really can't get the full gist of a book by reading only half of each sentence, huh?

I wasn't aware that you could close the parentheses outside the carriage return

Why not? It's text, just like "Starting Mileage: ". To make it depend upon the test, include it inside the result of the If() function.

You can also use the List() function to achieve the same result:

List (

If ( not IsEmpty ( Mileage_Starting ) ; "Starting Mileage: " & NumToJText ( Mileage_Starting ; 1 ; 0 ) ) ;

If ( ... ) ;

If ( ... ) ;

...

)