SqLite printf problem

I’m having a problem with the SqLite printf element, which I need to use to format part of the result in an SqLite query.

My code is:

var rs as RowSet = app.db.SelectSQL("
SELECT person.id, person.name, person.surname, person.age, sum(Invoices.amount)
FROM invoices
INNER JOIN person on person.id = invoices.customer
GROUP by person.surname
ORDER by " + Sortby)

I’ve split the query into separate lines to aid readability. It is one line in the original

Sortby is a variable containing one of the fieldnames, to determine the sort order. The rowset is displayed in a 5 column listbox.

This code works fine, but if I change ’ sum(Invoices.amount) ’ to ’ printf("%.2f", sum(Invoices.amount)) ’ I get errors, the first of which is that there is a syntax error in this line of code. I need to do this so that I can haver the sum of Invoices figure formatted to 2 places of decimals.

I have used DB Browser for SqLite to test the query, and it works fine with ’ printf("%.2f",sum(Invoices.amount)) ’ & formats the output as it should.

Is there some other way of formatting output in this situation?

Many thanks for any ideas!

If you are passing this overall string to a Xojo method such as SelectSQL, you will need to double up your double-quote characters around the %.2f, thus:

printf(""%.2f"", sum(Invoices.amount))

Thanks you so much! That works perfectly now.

I just wonder if I could have found that in the Xojo documentation ofr elsewhere! I haven’t seen it mentioned

Again many thanks

It’s on the page for string literals (though unless you know what you’re looking for, that might not be easy to find).

Also, since your question was answered would you be so kind as to mark the answer? Just click “Solution” under the post that was the answer. This helps our community know that the thread was answered! :slight_smile:

Many thanks again. I don’t think I would ever have found that!

Solution clicked as requested

1 Like