Change Query in SQL

[Bubsi]

I use the following code in a push-button method in a form.
Now I want to use sql-commands but I don't know how to use the "qBill" variable in the sql commands.
I have also problems to change the "if not qvar.executeQBE". Is there an equal command for sql ?

The actual syntax is:
var
qVar Query
r report
qBill string
endvar

if billNo.value = ""
then qBill = "" ;get all bills
else qBill = ", "+billNo.value ;only the bill you want
endif

qvar = Query
Account.DB | Number | Date | Client |
| Check ~qBill | Check | Check |
EndQuery

if not qvar.executeQBE("riv:answer.db&quot
then errorShow()
endif

 

[carstenFH]

Hi Bubsi

If I ain't wrong, this should do it.

var
qVar Query
db Database
mySQL SQL
r report
qBill string
endvar

if billNo.value = ""
then qBill = "''" ;get all bills
else qBill = "'" +billNo.value + "'" ;only the bill you want
endif


db.open(":WORK:&quot
mySQL = SQL

SELECT Number, Date, Client
FROM Account
WHERE Number = ~qBill
ORDER BY Number, Date, Client

endSQL

if not mySQL.executeSQL(db,"riv:answer.db&quot then
errorShow("executeSQL failed&quot
endIf

 

[Bubsi]

Hi carstenFH,

thanks for your help but it doesn't work.
Perhaps the ~qBill will be the problem.
May I send you the complete commands?

 

[carstenFH]

Yes - that's all right with me.

You could also try copy/paste this, and see if it takes any effect.

if billNo.value = ""
then qBill = "''" ;or change it to: qBill = ""
else qBill = "'," +billNo.value + "'" ;or change it to: qBill = "," +billNo.value
endif

There was a comma in qBill, which I belived did no good, so I removed it - might make the difference.
The code I posted earlier worked in the setup I made to test it.

else cfh@post5.tele.dk

Carsten

 

[Bubsi]

Hi Carsten,

thanks a lot.

With the following code it worked without any problems.

if billNo.value = ""
then qBill = "''" ;get all bills
else qBill = "'"+billNo.value+"'" ;only the bill you want
endif