how to get opening and closing balance from buy & sale of any stock and to get it in the next date

[AKVERMA111]

SELECT TBLTRANSACTIONS.TRX_ID, TBLTRANSACTIONS.TRXDATE, TBLTRANSACTIONS.SCRIPNAME, TBLMYSCRIPS.SCRIP_ID, TBLTRANSACTIONS.BUYQTY, TBLTRANSACTIONS.SOLDQTY
& CCur(DSum("BUYQTY","TBLTRANSACTIONS","(TRX_ID<= [TRX_ID]" OR TRXDATE<>#" [TRXDATE],"#) AND TRXDATE<=#" [TRXDATE], "#) AS OPENINGBALANCEQTY
FROM TBLMYSCRIPS INNER JOIN TBLTRANSACTIONS ON TBLMYSCRIPS.SCRIP_NAME=TBLTRANSACTIONS.SCRIPNAME


ORDER BY TBLTRANSACTIONS.TRXDATE DESC , TBLTRANSACTIONS.SCRIPNAME DESC , TBLMYSCRIPS.SCRIP_ID DESC;

thiscode is notworking and telling an error which I am not able to sort\out and rectify
any help please thanks a lot

[Micron]

Always post the error message and its number if applicable. In absence of any other clue, I'll say
- remove the quotes around your field/table names and replace with [ ].
- you have quotes after commas between # as well - I presume the date fields are date data type and not text. Even so, the comma placement looks wrong.
- I count a different number of ( versus ) brackets.
That's my take without knowing what the error is.

[ItsMe]

...- remove the quotes around your field/table names and replace with [ ]....

I would not recommend this. In order for the DSum() function to work correctly, you will need to pass a string expression.

Having said that, the second half of the function looks very wrong. Where Micron mentions the quotes after the comma and the #, like this =#" [TRXDATE], "#)

In addition I am not counting the correct number of parenthesis. With the multiple functions, you have the incorrect number. Then, when you consider the AND plus the OR operators, you do not have the correct number of parenthesis. Maybe create a second query object for the AND's and OR's and use the Query Object name as a subquery would be less confusing.

I would ask if the conversion is needed. Maybe try it without the CCur. I say this because Access may be able to do the Cast automatically. Also, it seems you are trying to Cast a field that is named BUYQTY. This does not seem like a currency. Even if it is a currency, it is not likely mandatory to include it in the SQL.