get equivlant for q7

[GaryVass]

In my kbasic program I had a statement that read:

gstDeposit = Get("SELECT SUM(AMOUNT)FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(" & Month(sdteTo) & ") AND YEAR(" & Year(sdteTo) & ";")


But get is not implemented yet in q7basic.... any ideas on a workaround?

[Henning]

I haven't tested, but from examples database: rslt = Query.Command("SELECT * FROM address")

In your case:
gstDeposit = Query.Command("SELECT SUM(AMOUNT)FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(" & Month(sdteTo) & ") AND YEAR(" & Year(sdteTo) & ";")

/Henning

[GaryVass]

Well, I almost have it working ( I think)... I have the following lines now:

If perfrep = "YES" Then
gstDeposit = Query.Command("SELECT SUM(AMOUNT)FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(" & Month(DateValue(sdteTo)) & ") AND YEAR(" & Year(DateValue(sdteTo)) & ");")
End If

sdteTo is dimmed as a string. It is ok with MONTH(" & Month(DateValue(sdteTo)) but gives an error at YEAR(" & Year(DateValue(sdteTo)) & ") telling me I have incompatible types.... ????

Is there a typo there that I am not seeing? I am running this on the LInux version of Q7.

[Slowdown]

Hi GaryVass,

Try to declate sdteTo as DateTime.
Now you are trying to place a date value into a String that is what Q7B telling you.

[Henning]

Is sdteTo formatted as "yyyy-mm-dd" ? I guess DateValue() expects a date in the international format, so does SQL.

/Henning

[Henning]

This works ok.

Code: Select all

Sub ShowDate()
  Dim s As String

  s = "2013-01-13"
  label_1.Text = "Year:" & Year(DateValue(s)) & "  Month:" & Month(DateValue(s)) & "  Day:" & Day(DateValue(s))
End Sub


OBS! Query.Command returns a Dictionary!! Not the expected Recordset...

/Henning

[Henning]

Also recomended to work with DB's in Q7B, play with the examples Database and Database/SQL.

/Henning

[GaryVass]

I have been looking at the examples, Databse and SQL, neither will finish compiling and running on my machine.

What does OBS mean? IT returns a dictionary? So, it will not return the calculated field from mysql, as I had hoped?

Thanks!

[Henning]

Both examples compiles and run on my windows machine. The one in Database doesn't do much usable, but the one in SQL does. It complains about missing DB if you run the MySQL example. I haven't tried, but I guess that if the DB is created it will work.

If selecting SqLite, it has a msgbox that shows how the Dictionary is orderd though.

/Henning

[GaryVass]

Ok, I am still having errors with this line:

gstDeposit = Query.Command("SELECT SUM(AMOUNT) FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(" & tempMonth & ") AND YEAR(" & tempYear & ");")


tempMonth and tempYear a declared as string

The error I get is:

Q7Basic: syntax error or parser error - types incompatible near ') End' in line 416 in file Reports.Module.q7b

But it looks like it is reporting the word End as being in the statement, and it is not..

any ideas? I would really like to get past this point.

Thanks