[Solved] Dateadd syntax on the criterion field

[d_h159@yahoo.com]

I am trying to get a return from a query that will provide the result of only dates that falls between today's date and 30 days prior from today's date.. I would also like to be prompted to enter a date (e.g. input parameter) when the query is run. I would like to use the dateadd function on the criterion field in the QBE grid to accomplish this. Can anyone help me to accomplish this task. I rather not have the SQL syntax if possible. If the current_date function Is plausible, then I could use that as well. The ultimate goal is to have the parameter used on the criterion field, because after the query Is run, the sql syntax will populate inadvertently. Please assist me with this endeavor.

[Villeroy]

You are using this very old database software which is shipped with your office suite: http://www.hsqldb.org/doc/1.8/guide/ch09.html and which does not provide any dateadd function.

The zip file https://www.mediafire.com/?wj4l1n2geqp66w6 contains a folder with your database converted to a stand-alone HSQLv2 database. Apart from additional functionality (e.g. dateadd), this solution minimizes the risk of data loss and you may run it later with a HSQL server for multi-user access.

http://hsqldb.org/doc/guide/builtinfunc ... _functions

Extract the zip to a trusted folder (Tools>Security>Macro Security...) and run Tools>Macros>Basic... [Medications_Tracking.odb] > Standard > AutoInstall > onDocumentOpen This macro will adjust the database connection and the driver setting to the new path on your system.

[d_h159@yahoo.com]

Thank you so much. I appreciate the quick response and I will download the new version. Should I uninstall the old version from my pc, then extract the files?

[Villeroy]

The old version of HSQL is part of your office suite and it is the only one which works with embedded databases. Just keep it. The one I uploaded has its database in the database folder and its own HSQL2 driver in the driver folder. The driver is just one 1.5 MB file hsqldb.jar.
Of course you can download the full HSQL package which includes the full documentation, source code and additional tools. Operating your Base document requires no more than one file hsqldb.jar and the documentation is availlable online.

I stored your file in its own directory, added the driver folder with hsqldb.jar, opened your file and called this tool of mine to extract and reconnect your database. Then I added 2 Basic modules from this other tool of mine. That Basic code can reconnect the database document to its database and driver when the base folder has been moved. //however, the second snippet is not really required since the extraction code can also reconnect a database//