Quote from Gaev on March 22, 2019, 8:23 pm

@mazzu001:

I get the error "cannot perform this operation on a closed dataset".

You don't say what kind of database (Microsoft Access or other SQL DB) you are working with. Did you do a dbpOpenDatabase or dbpOpenAccessDatabase command (somewhere else) before invoking the afore mentioned commands ?

tried a few variations

If you did open the database in question, try these variations in order to zoom in on the problematic command ...

1) try and open the expenses table ...

dbpOpenTable "invoicerlite" "expenses" ""
dbpShowGrid "invoicerlite" "expenses" "Rectangle10"

... if you get the same error ...

a) your database was NOT opened successfully

b) your expenses table does not exist; check using dbpGetTableNames

 

2) Otherwise, try a dbpExecSQL command that will select all records ...

dbpExecSQL "invoicerlite" "SELECT * FROM expenses" "datetest" 
dbpOpenTable "invoicerlite" "datetest" ""
dbpShowGrid "invoicerlite" "datetest" "Rectangle10"

... if that does NOT work, check that table datetest was actually created; check using dbpGetTableNames.

 

3) But if that worked, try your dbpExecSQL command using hard coded dates (instead of a variable) ... note that the Microsoft Access database only works with American format dates (mm/dd/ccyy).

See if that creates datetest Table with matching records.

Let us know results of your tests.

Quote from mazzu001 on March 24, 2019, 7:35 pm

Hi Gaev,

Thanks for the input. Yes all tables are opened in the startup procedure. The database itself was created with Dbpro usingdbproCreateAccessDaabase so its an access database. The error stems from the sql statement. If I remove the OpenTable and ShowGrid commands it still gives me the error, so the error is definitely coming from the sql command. I have tried doing a simple SELECT * FROM table and that works fine. If I use a WHERE [variable] = something that works fine too. So the WHERE function is fine. But as soon as I put the WHERE and BETWEEN in the same command this error appears. I know this is doable as I have it running fine on some ASP code. So the Syntax appears sound. I just cant seem to do it in VisualNeo Win.

I also did try putting in actual dates in the form of mm/dd/yyyy like '01/15/2018' instead of a variable and I still get the error.

Quote from Gaev on March 25, 2019, 4:11 pm

@mazzu001:

Thanks you for the detailed report on your findings it helps to narrow down the root cause of the problem.

1) Does the dbpExecSQL command work if you do a query involving a date field but excluding the BETWEEN clause e.g. ...

ExpenseDate equals some dd/mm/ccyy

or

ExpenseDate less than some dd/mm/ccyy

2) Try using mm/dd/yy instead of mm/dd/ccyy

3) Try placing an ! before the SELECT (in case VisualNEOWin is interpreting the / as a divisor and attempting to do math on it).

4) Taking a cue from here ... http://allenbrowne.com/ser-36.html ... which is using "InvoiceDate > #12/31/2000#" ... try surrounding the hard coded date with # characters.

5) And from here ... https://support.microsoft.com/en-ca/help/826766/how-to-make-a-change-to-dates-by-using-functions-and-operators-in-acce ... try ...

SELECT * FROM expenses
WHERE ExpenseDate >= DateValue(" 10/01/1993")AND ExpenseDate <= DateValue("12/31/1993");

 

Let us know your findings.

Quote from mazzu001 on March 26, 2019, 4:58 pm

This looks promising. Thanks very much Gaev for all the help. I am out of town until tomorrow but I will test this out as soon as I am back in the office. Will let you know my findings.

Quote from mazzu001 on March 26, 2019, 9:43 pm

dbpExecSQL "invoicerlite" "SELECT * FROM expenses|WHERE ExpenseDate >= DateValue([#34][startdate][#34])AND ExpenseDate <= DateValue([#34][enddate][#34])" ""

This was the winner!! Thank you very much for the help. Works like a charm.