get equivlant for q7

[GaryVass]

Ok,

I found the 'end' in the line... it was part of the next line that was an End If

Not sure why Q7Basic was combining the lines, BUT I put another return in their and that error went away.

Now, I get this error:

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

so, how is a ) incompatible??

[Henning]

Shouldn't the query end with AND MONTH = something AND YEAR = something?

Something like:
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)) & ")"

I don't think the semi is needed with Query.Command.

What are the fields MONTH & YEAR declared to in the DB?

Also, Db, Table and Field-names with spaces will bite you sooner or later.


I am slowly working on an example using the Records Object.... Seems able to work close to how KB did.

/Henning

[GaryVass]

Well, I have the 'AND YEAR' and the 'AND MONTH' in the statement.

I have tried to remove the ; but it made no change.

I also changed the YEAR( to YEAR = and YEAR = ( and the month command respectively, and no change.

I have been removing spaces in the database names... I no longer use spaces, but these ones are older and have not been changed as of yet.

[Henning]

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 & ");")

From the above I read table fields as:
AMOUNT, TRANSACT, MONTH and YEAR in table tempReport.
Is that what you have?

If so, what are the MONT and YEAR fields declared as?

/Henning

[GaryVass]

MONTH and YEAR are not declared, as they are mysql commands.

BUT I cut everything out of the line but gstDeposit = Query.Command("SELECT SUM(AMOUNT) FROM tempReport ;") and I stil get an error....Q7Basic: syntax error or parser error - types incompatible near ') ' in line 417 in file Reports.Module.q7b

I am running the Linux version, and I don't understand why I keep getting this error.

Also, (off topic, but mysql related) I tried the line in a mysql cmd window,
SELECT SUM(AMOUNT) FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(11) AND YEAR(2013);

and also as:
SELECT SUM(AMOUNT) FROM tempReport WHERE TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS' AND MONTH(tDATE)=11 AND YEAR(tDATE)=2013;


The result is the the same, just a simple sumation of the field AMOUNT. Can anyone tell me how to limit the summation to only the selected month / year?

[Henning]

From the MySQL manual:
MONTH(date)

Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part.

mysql> SELECT MONTH('1998-02-03');
-> 2

YEAR(date)

Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date.

-> 2008

The date parameter expects a string "2013-04-24"

You could try to use parens, and correct the YEAR & MONT part.
SELECT SUM(AMOUNT) FROM tempReport WHERE (TRANSACT = 'CASH RECEIPTS' OR TRANSACT = 'DEPOSIT' OR TRANSACT = 'WITHDRAW FUNDS') AND (MONTH("2013-04-24")=4 AND YEAR("2013-04-24")=2013);

/Henning

[GaryVass]

Hmmm,

Could I just try to use a date comparison: select...... where tempdate >= datefield and tempdate <= datefield2 ?

This did work in kbasic, (with the old syntax).

Also, in my previous post, I stripped most of the commands out of the query.select line, and I still get an error... does the query.select work in the linux version of q7basic?

[Henning]

If you wish, you can use attached project as an example on accessing DB's "the old KB way" What's coded so far works.

DON'T try to ReDim the array of UserTypes!!
Just came to my mind (untested) to have a Sub in Global with a size parameter to ReDim the array... No it didn't work either...

MySQL_UDTArr_Test.zip

(3.78 KiB) Downloaded 71 times

/Henning

[Henning]

Aaaaa...

Missed this obvious thing: SELECT SUM(AMOUNT) AS somename FROM...

/Henning

[Slowdown]

allll right,
never never did anything with SQL.
If i run the example for the first time it reports DB <"skidata"> does not Exist"
That one i understand, then i fill in some data in the QlineEdit's and hit 'cmdCreateTbl'
i presume that when i hit that button the data is stored in the DB ?
When i run the example again it presents the messagebox with skidata does not exist.
Did i missed something ?