Subject Re: converting text to value in SQL
From Mervyn Bick <invalid@invalid.invalid>
Date Mon, 17 Jan 2022 23:08:50 +0200
Newsgroups dbase.getting-started

On 2022/01/17 20:16, Rouke wrote:
> how do I do this in sql:
> str(val(trapnumber),2,0,'0')

Unfortunately you don't. :-(  You have to create the string using XDML
and then feed the string into your SQL statement.

It depends what you want to do.

If you have a character field containing values from '00' to '99' and
you need to select records you can use Heinz's suggestion or you can use
a parameter driven query.  Your code will return ** if you feed it a
string longer than two digits.

form.q = new query()
form.q.sql = [select * from YOURTABLE where FIELD2 = :trap]
form.q.params['trap'] = str(val(trapnumber),2,0,'0') = true

To fetch a different set of data, with Heinz's suggestion you would need
to deactivate the query, restate the sql statement and then re-activate
the query.  It's one way of doing things and many programmers do it this
way.  On the other hand, with parameters all you need is to replace the
parameter value and requery().

form.q.params['trap'] = '23' //or code to generate '23'